Menu
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
697
But yes, you can use two WHERE.
You can count multiple COUNT() for multiple conditions in a single query using GROUP BY. SELECT yourColumnName,COUNT(*) from yourTableName group by yourColumnName; To understand the above syntax, let us first create a table. The query to create a table is as follows.
The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions.
To count the number of different values that are stored in a given column, you simply need to designate the column you pass in to the COUNT function as DISTINCT . When given a column, COUNT returns the number of values in that column. Combining this with DISTINCT returns only the number of unique (and non-NULL) values.
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 |
SELECT
product_id,
COUNT(CASE WHEN type_id=1 THEN 1 END) count_type_1,
COUNT(CASE WHEN type_id=2 THEN 1 END) count_type_2,
COUNT(CASE WHEN type_id=3 THEN 1 END) count_type_3,
COUNT(CASE WHEN type_id=4 THEN 1 END) count_type_4,
COUNT(CASE WHEN type_id=5 THEN 1 END) count_type_5,
MONTH(date) month,
YEAR(date) year
FROM
products
GROUP BY
product_id,
MONTH(date),
YEAR(date)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
