SQL Joining Of Queries

Question

I have the below query and each section of it details stock in and out. What I would like is to have a table as I have shown below with each query detailing moves in and out for a date.

 SELECT 
 inventory.DSTAMP,
 inventory.NAME,
 inventory.WEIGHT  AS KG_Weight,
 inventory.CODE
 FROM inventory
 WHERE inventory.code = 'In'

 UNION

 SELECT 
 inventory.DSTAMP,
 inventory.NAME,
 inventory.WEIGHT  AS KG_Weight,
 inventory.CODE
 FROM inventory
 WHERE inventory.code = 'Out'

Output to be:

╔═════════╦════════════╦═════════════╗
║ Date    ║ In         ║ Out         ║
╠═════════╬════════════╬═════════════╣
║01/01/14 ║ 4500       ║ 7800        ║
║02/01/14 ║ 0          ║ 8500        ║
║03/01/14 ║ 8000       ║ 0           ║
╚═════════╩════════════╩═════════════╝

Is there an easy way to do this?

Answer 1

You can use conditional aggregation:

SELECT i.DSTAMP, i.NAME,
       SUM(CASE WHENn i.CODE = 'IN' THEN i.WEIGHT END) as IN_KG_Weight,
       SUM(CASE WHENn i.CODE = 'OUT' THEN i.WEIGHT END) as OUT_KG_Weight
FROM inventory i
WHERE i.code = 'In'
GROUP BY i.DSTAMP, i.NAME;

EDIT:

To group this just by date:

SELECT to_char(i.DSTAMP, 'YYYY-MM-DD') as yyyymmdd, i.NAME,
       SUM(CASE WHENn i.CODE = 'IN' THEN i.WEIGHT END) as IN_KG_Weight,
       SUM(CASE WHENn i.CODE = 'OUT' THEN i.WEIGHT END) as OUT_KG_Weight
FROM inventory i
WHERE i.code = 'In'
GROUP BY to_char(i.DSTAMP, 'YYYY-MM-DD'), i.NAME;

This converts the value to a date string, which is fine for ordering.

Answer 2

select DSTAMP,
       sum(case when code = 'In' then WEIGHT else 0 end) as "In",
       sum(case when code = 'Out' then WEIGHT else 0 end) as "Out"
from inventory
group by DSTAMP