CODEWITHSUNDEEP
sqloracle-database
Komarov
Komarov

Reputation: 35

SUM records by hour + add subqueries

So i've got this code:

SELECT to_char(dstamp, 'HH24') as HOUR, SUM(update_qty) total_received
FROM inventory_transaction
WHERE dstamp BETWEEN to_date('28/05/2021 18:00:00', 'dd/mm/yyyy hh24:mi:ss')  
                 AND to_date('29/05/2021 06:00:00', 'dd/mm/yyyy hh24:mi:ss') 
AND code = 'Receipt'
GROUP BY to_char(dstamp, 'HH24')
ORDER BY HOUR ASC  ;

Im trying to add an additional column to it, thats gonna be showing me total_putaway, so exactly the same query, but code = 'Putaway'. I was trying to do it by the CROSS JOIN, or some subqueries but it doesent really work, as i want to group my records by hour so 12 rows in total.

Upvotes: 1

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Use conditional aggregation:

SELECT to_char(dstamp, 'HH24') as HOUR,
      SUM(CASE WHEN code = 'Receipt' THEN update_qty END) as total_received,
      SUM(CASE WHEN code = 'Putaway' THEN update_qty END) as total_putaway
FROM inventory_transaction
WHERE dstamp BETWEEN to_date('28/05/2021 18:00:00', 'dd/mm/yyyy hh24:mi:ss') AND
                     to_date('29/05/2021 06:00:00', 'dd/mm/yyyy hh24:mi:ss') 
GROUP BY to_char(dstamp, 'HH24') 
ORDER BY HOUR ASC

Upvotes: 1

Related Questions