jeonatl3 jeonatl3 - 21 days ago 8
SQL Question

SQL: Grouping data with sub-query

Hoping someone can help me with this query.

If I have a sample table:

orderId | itemId | quantity | created
123456 | 1 | 100 | 1478822402
123457 | 1 | 5 | 1478736001
123458 | 2 | 10 | 1478736001
123459 | 2 | 40 | 1478822402


I am trying to get a result set which gives me the sum of the quantities of items sold today and yesterday - grouped by item. For example:

item | numSoldToday | numSoldYesterday
1 | 100 | 5
2 | 40 | 10


Ignoring the hard coded timestamps for testing purposes, I can do this for just items sold today using:

SELECT item, sum(quantity) as numSoldToday FROM orders
WHERE created >= 1478822400 AND created <= 1478908799
GROUP by item


And I can do do this for just ONE item sold today vs. yesterday using a subquery:

SELECT sum(quantity) as numSoldToday,
(SELECT (sum(quantity) FROM orders WHERE created >= 1478736000 AND created <= 1478822399 AND item = 1) as numSoldYesterday
FROM orders
WHERE created >= 1478822400 AND created <= 1478908799 AND item = 1


But I can't seem to figure out how to combine the two - a grouped result set of each item with the number sold today vs. yesterday without using the client side programming language to get a list of items and loop through each one which seems a bit inefficient.

I tried putting a GROUP BY in the subquery but this just gave me an error and I can't think of the best way to proceed with doing this purely with SQL.

Any ideas welcome!
Thanks :-)

Answer

You can do this in an aggregated case statement rather than subqueries, something like this;

SELECT
o.item
,SUM(CASE WHEN o.created >= 1478822400 AND o.created <= 1478908799 THEN quantity ELSE 0 END) numSoldToday
,SUM(CASE WHEN o.created >= 1478736000 AND o.created <= 1478822399 THEN quantity ELSE 0 END) numSoldYesterday
FROM orders o
GROUP BY o.item
Comments