CODEWITHSUNDEEP
sqlsqlite
jeonatl3
jeonatl3

Reputation: 595

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 :-)

Upvotes: 1

Views: 47

Answers (1)

Rich Benner
Rich Benner

Reputation: 8113

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

Upvotes: 1

Related Questions