Donnie Darko Donnie Darko - 2 months ago 6
MySQL Question

How to write this query MYSQL

I have this database:

| id | name | email | control_number | created | | | | | |
|:--:|-------|-----------------|----------------|------------|---|---|---|---|---|
| 1 | john | john@gmail.com | 1 | 14/09/2016 | | | | | |
| 2 | carl | carl@gmail.com | 1 | 13/08/2016 | | | | | |
| 3 | frank | frank@gmail.com | 2 | 12/08/2016 | | | | | |


And i want to get the COUNT in the last 12 months by the control_number.

basicly is a COUNT where control_number = 1 but by month.

So if the query is done today, its september, it should start from september to October 2015 and display the count of records for each month.

Result should be:

09/2016 = 50
08/2016 = 35
07/2016 = 20
06/2016 = 50
05/2016 = 21
04/2016 = 33
03/2016 = 60
02/2016 = 36
01/2016 = 11
12/2015 = 0
11/2015 = 0
10/2015 = 0

Answer

Hmmm. Getting the 0 values can be tricky. Assuming that you have some data each month (even if not for "1"), th en you can do:

select extract(year_month from created) as yyyymm,
       sum(control_number = 1)
from t
where created >= date_sub(curdate(), interval 12 month)
group by extract(year_month from created)
order by yyyymm;

If you don't have at least one record for each month, then you'll need a left join and a table with one row per month.

Comments