Multiple Query results in single table

Question

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

Answer 1

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