CODEWITHSUNDEEP
sql
JeffreyLazo
JeffreyLazo

Reputation: 853

Multiple Query results in single table

I have this query:

select sum(QTYINSTOCK * AVGPRICE) as Albums 
from inventoryinfo
WHERE Category = 'Albums'

It returns:

Albums
$660.80

Is there a way for me to run multiple queries in one query and return in a single table? For example:

select sum(QTYINSTOCK * AVGPRICE) as Albums from inventoryinfo
WHERE Category = 'Albums'
select sum(QTYINSTOCK * AVGPRICE) as Prints from inventoryinfo
WHERE Category = 'Prints'
select sum(QTYINSTOCK * AVGPRICE) as Frames from inventoryinfo
WHERE Category = 'Frames'


Albums  |  $660.80
Prints  |  $123.00
Frames  |  $67.00

====== Looks Like the concensus is to use a union The results are not quite formatted as I would like, it returns

Albums
660.80
123.00
67.00

Upvotes: 3

Views: 11537

Answers (3)

D Stanley
D Stanley

Reputation: 152511

Just use a GROUP BY:

select Category, sum(QTYINSTOCK * AVGPRICE) as Total from inventoryinfo
WHERE Category IN ('Albums', 'Prints', 'Frames')
GROUP BY Category

Or if you want the results in multiple columns:

select 
    SUM(CASE WHEN Category ='Albums' THEN QTYINSTOCK * AVGPRICE ELSE 0 END) as Albums END
    SUM(CASE WHEN Category ='Prints' THEN QTYINSTOCK * AVGPRICE ELSE 0 END) as Prints END
    SUM(CASE WHEN Category ='Frames' THEN QTYINSTOCK * AVGPRICE ELSE 0 END) as Frames END
FROM inventoryinfo
WHERE Category IN ('Albums', 'Prints', 'Frames')
GROUP BY Category

Upvotes: 12

Kevin Kunderman
Kevin Kunderman

Reputation: 2134

You could uses a Union to do this. Keep in mind that each select must return the same amount of columns if you later decide to add extra columns to your results.

select sum(QTYINSTOCK * AVGPRICE) as Albums, 'Albums' as Type from inventoryinfo
WHERE Category = 'Albums'
UNION ALL
select sum(QTYINSTOCK * AVGPRICE) as Prints ,'Prints' as Type from inventoryinfo
WHERE Category = 'Prints'
UNION ALL
select sum(QTYINSTOCK * AVGPRICE) as Frames,'Frames' as Type from inventoryinfo
WHERE Category = 'Frames'

Upvotes: 0

R Hill
R Hill

Reputation: 1804

The union operator:

select "ten", 10
union
select "twenty", 20
union
select "thirty", 30

produces

ten    | 10
twenty | 20
thirty | 30

Upvotes: -1

Related Questions