I have a table like this

ID,date,state    
1,2015-04-04,1    
1,2015-05-04,0

and I would like to make a request like this:

SELECT 
(YEAR(date)) AS year_A, 
(MONTH(date)) AS month_A, 
(count(*)) AS number_A,
(count(*) WHERE state = 0) AS number_B
(count(*) WHERE state = 1) AS number_C
 FROM A_transaction GROUP BY year_A, month_A

to get something like this

year_A,month_A,number_A,number_B,number_C    
2015,04,2,1,1

But this doesn't work. I think the problem is from the WHERE clause

2 Answers

4
Nick On Best Solutions

You need to write your query using conditional aggregation (i.e. COUNT only when a condition is true). This will give you the results you want:

SELECT YEAR(date) AS year_A, 
       MONTH(date) AS month_A, 
       count(*) AS number_A,
       count(CASE WHEN state = 0 THEN 1 END) AS number_B,
       count(CASE WHEN state = 1 THEN 1 END) AS number_C
FROM A_transaction 
GROUP BY year_A, month_A

Note you don't need to enclose expressions in parentheses. They just make your query harder to read.

Note also that standard MySQL date format is YYYY-MM-DD which means that the two sample dates in your table are actually in different months, and the output of the query is

year_A  month_A number_A    number_B    number_C
2015    4       1           0           1
2015    5       1           1           0

If your dates are in YYYY-DD-MM format, you will need to convert them using STR_TO_DATE e.g.

SELECT YEAR(STR_TO_DATE(date, '%Y-%d-%m')) AS year_A, 
       MONTH(STR_TO_DATE(date, '%Y-%d-%m')) AS month_A, 
       count(*) AS number_A,
       count(CASE WHEN state = 0 THEN 1 END) AS number_B,
       count(CASE WHEN state = 1 THEN 1 END) AS number_C
FROM A_transaction 
GROUP BY year_A, month_A

Demo on dbfiddle

1
forpas On

MySQL makes it very easy to get aggregated columns like these, because it evaluates expressions like state = 0 to 0 (false) or 1 (true).
So you don't need CASE..WHEN and you can do it like this:

select 
  year(date) as year_A, 
  month(date) as month_A, 
  count(*) as number_A,
  sum(state = 0) as number_B,
  sum(state = 1) as number_C
FROM A_transaction 
GROUP BY year_A, month_A