Nsryjdtyk

I am not sure how I can get this code to have three columns for each category (Portrait,Landscape, and Abstract). As of now, it only has one column and all three of these values computed values:

SELECT SUM(Price) AS TotalSales_Portrait

FROM Photo

WHERE TransID IS NOT NULL AND PhotoID IN (

SELECT PhotoID

FROM Models) 



UNION



SELECT SUM(Price) AS TotalSales_Landscape

FROM Photo

WHERE TransID IS NOT NULL AND PhotoID IN (

SELECT PhotoID

FROM Landscape)



UNION



SELECT SUM(Price) AS TotalSales_Abstract

FROM Photo

WHERE TransID IS NOT NULL AND PhotoID IN (

SELECT PhotoID

FROM Abstract)

Query output

Any advice is greatly appreciated

This can be done by using pivot as follows

select *

from (

SELECT MAX('Portrait') as photo_type,SUM(Price) AS Total_Sales

  FROM Photo

WHERE TransID IS NOT NULL AND PhotoID IN (

SELECT PhotoID

  FROM Models) 

UNION

SELECT MAX('Landscape'),SUM(Price) AS Total_Sales

FROM Photo

WHERE TransID IS NOT NULL AND PhotoID IN (

SELECT PhotoID

FROM Landscape)

)x

pivot (sum(total_sales) for photo_type in(Portrait,Landscape))y

Thank you George, this solved my problem. Here is the final code I have after revising yours:

select *

from (

SELECT MAX('Portrait') as photo_type,SUM(Price) AS Total_Sales

  FROM Photo

WHERE TransID IS NOT NULL AND PhotoID IN (

SELECT PhotoID

  FROM Models) 

UNION

SELECT MAX('Landscape'),SUM(Price) AS Total_Sales

FROM Photo

WHERE TransID IS NOT NULL AND PhotoID IN (

SELECT PhotoID

FROM Landscape)

UNION

SELECT MAX('Abstract'),SUM(Price) AS Total_Sales

FROM Photo

WHERE TransID IS NOT NULL AND PhotoID IN (

SELECT PhotoID

FROM Abstract)

)x

pivot (sum(Total_Sales) for photo_type in(Landscape,Abstract,Portrait))y

If you have a photo type field in your Photos table it would be easier:

SELECT SUM(price), photo_type 

FROM Photos

JOIN Models 

ON Models.PhotoID = Photos.PhotoID

JOIN Landscape

ON Landscape.PhotoID = Photos.PhotoID

JOIN Abstract

ON Abstract.PhotoID = Photos.PhotoID

GROUP BY photo_type

If not, you could probably do something like:

SELECT SUM(price), type FROM (

  SELECT price, 'Models' as type from Photos 

  WHERE TransID IS NOT NULL AND PhotoID IN (

    SELECT PhotoID

    FROM Models

  )



  UNION



  SELECT price, 'Landscape' as type from Photos 

  WHERE TransID IS NOT NULL AND PhotoID IN (

    SELECT PhotoID

    FROM Landscape

  )



  UNION



  SELECT price, 'Abstract' as type from Abstract

) as photos

GROUP BY type

I haven't tested these queries, but they should be at least close to a working solution.

I think I would simply use subqueries:

SELECT (SELECT SUM(Price) AS TotalSales_Portrait

        FROM Photo

        WHERE TransID IS NOT NULL AND PhotoID IN (SELECT PhotoID FROM Models) 

       ) as TotalSales_Portrait,

       (SELECT SUM(Price) AS TotalSales_Landscape

        FROM Photo

        WHERE TransID IS NOT NULL AND PhotoID IN (SELECT PhotoID FROM Landscape)

       ) as TotalSales_Landscape,

       (SELECT SUM(Price) AS TotalSales_Abstract

        FROM Photo

        WHERE TransID IS NOT NULL AND PhotoID IN (SELECT PhotoID FROM Abstract)

       ) as TotalSales_Abstract;

Alternatively, if the secondary tables have no duplicates:

SELECT SUM(CASE WHEN m.PhotoId IS NOT NULL THEN price ELSE 0 END),

       SUM(CASE WHEN l.PhotoId IS NOT NULL THEN price ELSE 0 END),

       SUM(CASE WHEN a.PhotoId IS NOT NULL THEN price ELSE 0 END)

FROM Photos p LEFT JOIN

     Models m

     ON m.PhotoID = p.PhotoID LEFT JOIN

     Landscape l

     ON l.PhotoID = p.PhotoID LEFT JOIN

     Abstract a

     ON a.PhotoID = p.PhotoID