Selecting counts of multiple items, grouped by date

Question

I have the following two tables as a part of my order processing system:

order(id, date)
order_item(order_id, item_id, type)

The type is an enum which denotes which table to look the order_item up in.

I want to write a query that selects the number of different item types ordered on each day. So it should produce something like:

Date         tshirts    dvds    mugs    cds
07/07/2010   3          6       2       2
10/07/2010   4          9       3       1
13/07/2010   1          2       1       9

Here is the query I currently have to select the count for one type. I just can't work out how to extend it to select the counts for multiple types.

SELECT DATE(order.date), COUNT(order.date)
FROM order, order_item
WHERE order.id = order_item.order_id AND order_item.type = 'tshirts'
GROUP BY DATE(order.date)

Answer 1

Sounds like a case for the SQL Server PIVOT command:

http://msdn.microsoft.com/en-us/library/ms177410.aspx

It's a bit tricky to get it right, but with a tiny bit of experimenting, you should get your result as needed.

Answer 2

It will not give you what exactly what you want

SELECT DATE(order.date), COUNT(order_item.type) as count , order_item.type as type
FROM order, order_item
WHERE order.id = order_item.order_id AND order_item.type = 'tshirts'
GROUP BY DATE(order.date), order_item.type

it will give something like follwing

Date         type      count             
07/07/2010   tshirts    3       
07/07/2010   dvds       6       
07/07/2010   mugs       2      
07/07/2010   cds        2
10/07/2010   tshirts    4       
10/07/2010   dvds       9       
10/07/2010   mugs       3      
10/07/2010   cds        1
13/07/2010   tshirts    1       
13/07/2010   dvds       2       
13/07/2010   mugs       1      
13/07/2010   cds        9

Two disadvantage I know are

1. Date will repeat
2. it will not count which are zero

Answer 3

Here is what works for me (with MySQL):

SELECT o.date, COUNT(if(oi.type='tshirts', 1, NULL)) as tshirts, COUNT(if(oi.type='mugs', 1, NULL)) as mugs
FROM order_item oi LEFT JOIN order o ON oi.order_id = o.id
GROUP BY o.date

Answer 4

Here is the query I ended up using:

SELECT DATE(order.date) AS day,
     (SELECT COUNT(order.date)
     FROM order, order_item
     WHERE DATE(order.date) = day AND
        order.id = order_item.order_id AND
        order_item.type = 'tshirt') AS tshirt,
    (SELECT COUNT(order.date)
     FROM order, order_item
     WHERE DATE(order.date) = day AND
        order.id = order_item.order_id AND
        order_item.type = 'mugs') AS mugs,
    etc.
FROM order
GROUP BY DATE(order.date)

It's incredibly slow, but it does the job.

Answer 5

You should not use the comma syntax for doing joins. Use the ISO syntax which uses the INNER JOIN keyword which can be shortened to JOIN.

Select O.Date
    , Sum( Case When OI.Type = 'tshirts' Then 1 Else 0 End ) As tshirts
    , Sum( Case When OI.Type = 'dvds' Then 1 Else 0 End ) As dvds
    , Sum( Case When OI.Type = 'mugs' Then 1 Else 0 End ) As mugs
    , Sum( Case When OI.Type = 'cds' Then 1 Else 0 End ) As cds
From Order As O
    Join Order_Item As OI
        On OI.order_id = O.id
Where OI.type In('tshirts','dvds','mugs','cds')
Group By O.Date