Single SQL to get multiple counts with different where clauses, is this possible?

count, mysql, sql, where-clause

Solution

You can use the following to pivot the data which uses an aggregate function and a `CASE` expression to create each column:

select 
  product_id,
  sum(case when type_id=1 then 1 else 0 end) count_type_1,
  sum(case when type_id=2 then 1 else 0 end) count_type_2,
  sum(case when type_id=3 then 1 else 0 end) count_type_3,
  sum(case when type_id=4 then 1 else 0 end) count_type_4,
  sum(case when type_id=5 then 1 else 0 end) count_type_5,
  month(date) month,
  year(date) year
from yourtable
group by product_id, month(date), year(date)

See SQL Fiddle with Demo

This gives the result:

| PRODUCT_ID | COUNT_TYPE_1 | COUNT_TYPE_2 | COUNT_TYPE_3 | COUNT_TYPE_4 | COUNT_TYPE_5 | MONTH | YEAR |
--------------------------------------------------------------------------------------------------------
|        300 |            1 |            0 |            0 |            0 |            0 |     1 | 2013 |
|        300 |            2 |            0 |            0 |            0 |            1 |     2 | 2013 |
|        300 |            1 |            0 |            0 |            0 |            0 |     3 | 2013 |
|        400 |            1 |            0 |            0 |            0 |            0 |     1 | 2013 |
|        400 |            2 |            0 |            1 |            0 |            2 |     3 | 2013 |
|        800 |            1 |            0 |            0 |            0 |            0 |     1 | 2013 |

Problem

I have a table as below, ``` +-----+--------+-----------+----------+ | id | type_id| product_id| date | +-----+--------+-----------+----------+ | 1 | 1 | 300 |22/01/2013| | 2 | 1 | 800 |22/01/2013| | 3 | 1 | 400 |30/01/2013| | 4 | 1 | 300 |05/02/2013| | 5 | 5 | 300 |27/02/2013| | 6 | 1 | 300 |28/02/2013| | 7 | 3 | 400 |12/03/2013| | 8 | 5 | 400 |02/03/2013| | 9 | 1 | 300 |06/03/2013| | 10 | 1 | 400 |06/03/2013| | 11 | 5 | 400 |06/03/2013| | 12 | 1 | 400 |08/03/2013| ``` Lets say if I want to get count of each type for each product group by month and year, Is this possible? Output that I'm after should be something similar to following.. ``` +-----------+------------+-------------+------------+------+------+ |product_id |count_type_1|count_type_3 |count_tyep_5| month| year | +-----------+------------+-------------+------------+------+------+ | 300 | 1 | 0 | 0 | 01 | 2013 | | 800 | 1 | 0 | 0 | 01 | 2013 | | 400 | 1 | 0 | 0 | 01 | 2013 | | 300 | 2 | 0 | 1 | 02 | 2013 | | 300 | 1 | 0 | 0 | 03 | 2013 | | 400 | 2 | 1 | 2 | 03 | 2013 | ``` Is this can be achieve using a single SQL? PS: This is on a MYSQL server

Original source