I have a table for my users that have a field named "created" that have the registration date.
How can i get a list that contains a count for the registrations number per month in last 12 months? Like this:
Month Count
1 1232
2 2222
3 122
4 4653
... ...
12 7654
I'm not used to working with mysql, so until now i just know how to count the number of registrations in last year, not how to group that count by last 12 months. Thanks in advance!
UPDATE
Now I'm getting this, using @fthiella solution:
+------------------------------+-------------------------------+----------+
| Year(FROM_UNIXTIME(created)) | Month(FROM_UNIXTIME(created)) | Count(*) |
+------------------------------+-------------------------------+----------+
| 2012 | 4 | 9927 |
| 2012 | 5 | 5595 |
| 2012 | 6 | 4431 |
| 2012 | 7 | 3299 |
| 2012 | 8 | 429 |
| 2012 | 10 | 3698 |
| 2012 | 11 | 6208 |
| 2012 | 12 | 5142 |
| 2013 | 1 | 1196 |
| 2013 | 2 | 10 |
+------------------------------+-------------------------------+----------+
How can i force query to give me the months with count = 0?
Solution by @fthiella (thanks a lot!):
SELECT y, m, Count(users.created)
FROM (
SELECT y, m
FROM
(SELECT YEAR(CURDATE()) y UNION ALL SELECT YEAR(CURDATE())-1) years,
(SELECT 1 m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) months) ym
LEFT JOIN users
ON ym.y = YEAR(FROM_UNIXTIME(users.created))
AND ym.m = MONTH(FROM_UNIXTIME(users.created))
WHERE
(y=YEAR(CURDATE()) AND m<=MONTH(CURDATE()))
OR
(y<YEAR(CURDATE()) AND m>MONTH(CURDATE()))
GROUP BY y, m;
And the results:
+------+----+----------------------+
| y | m | Count(users.created) |
+------+----+----------------------+
| 2012 | 5 | 5595 |
| 2012 | 6 | 4431 |
| 2012 | 7 | 3299 |
| 2012 | 8 | 429 |
| 2012 | 9 | 0 |
| 2012 | 10 | 3698 |
| 2012 | 11 | 6208 |
| 2012 | 12 | 5142 |
| 2013 | 1 | 1196 |
| 2013 | 2 | 10 |
| 2013 | 3 | 0 |
| 2013 | 4 | 0 |
+------+----+----------------------+
MONTHS_BETWEEN returns number of months between dates date1 and date2 . If date1 is later than date2 , then the result is positive. If date1 is earlier than date2 , then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer.
Query: SELECT YEAR(Order_date) AS Year, MONTH(Order_date) AS Month,SUM(Sales) AS Total_Sales FROM Products GROUP BY YEAR(Order_date), MONTH(Order_date) ; Here, we are simply grouping up the months and years using the GROUP BY clause and then getting the total sales using the SUM aggregate function.
How to Get Last 12 Months Sales Data in SQL. mysql> select * from sales where order_date> now() - INTERVAL 12 month; In the above query, we use system function now() to get current datetime. Then we use INTERVAL clause to filter those records where order_date falls after an interval of 12 months before present datetime ...
You can group month and year with the help of function DATE_FORMAT() in MySQL. The GROUP BY clause is also used.
If created is an INT field, you should use FROM_UNIXTIME function to convert it to a date field, and then MONTH function to extract the month:
SELECT Month(FROM_UNIXTIME(created)), Count(*)
FROM yourtable
WHERE FROM_UNIXTIME(created) >= CURDATE() - INTERVAL 1 YEAR
GROUP BY Month(FROM_UNIXTIME(created))
this will count all the rows that have been created in the last 12 months. Please notice that it's probably better to also group by the YEAR:
SELECT Year(FROM_UNIXTIME(created)), Month(FROM_UNIXTIME(created)), Count(*)
FROM yourtable
WHERE FROM_UNIXTIME(created) >= CURDATE() - INTERVAL 1 YEAR
GROUP BY Year(FROM_UNIXTIME(created)), Month(FROM_UNIXTIME(created))
If you need to count the registration numbers instead of the rows, you could use something like
COUNT(registration_number)
to skip null values, or
COUNT(DISTINCT registration_number)
to count only distinct ones.
Edit
If you also need to show months that have count=0, I would use a query like this that returns all of the months for the current and for the previous year:
SELECT y, m
FROM
(SELECT YEAR(CURDATE()) y UNION ALL SELECT YEAR(CURDATE())-1) years,
(SELECT 1 m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) months;
And then I'd use a LEFT JOIN, that returns all of the rows of the first query, and only the rows of the second query that matches:
SELECT y, m, Count(yourtable.created)
FROM (
SELECT y, m
FROM
(SELECT YEAR(CURDATE()) y UNION ALL SELECT YEAR(CURDATE())-1) years,
(SELECT 1 m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) months) ym
LEFT JOIN yourtable
ON ym.y = YEAR(FROM_UNIXTIME(yourtable.created))
AND ym.m = MONTH(FROM_UNIXTIME(yourtable.created))
WHERE
(y=YEAR(CURDATE()) AND m<=MONTH(CURDATE()))
OR
(y<YEAR(CURDATE()) AND m>MONTH(CURDATE()))
GROUP BY y, m
(please notice that here I am considering just the last 12 months, so if we are in the middle April 2013 it will count rows in the interval May 2012 - April 13, if this is not the correct behaviour please let me know)
SELECT MONTH(reg_date) , COUNT(reg_date)
FROM your_table
WHERE reg_date >= NOW() - INTERVAL 1 YEAR
GROUP BY MONTH(reg_date)
In case this helps anyone else, I additionally wanted this query but for a given time interval that was not necessarily the last 12 months (also with a timestamp for the date):
set @start='2013-05-01 00:00:00';
set @end='2015-03-31 00:00:00';
SELECT YEAR(FROM_UNIXTIME(yourtable.created_date)), MONTH(FROM_UNIXTIME(yourtable.created_date)), COUNT(yourtable.created_date)
FROM yourtable
WHERE created_date BETWEEN UNIX_TIMESTAMP( DATE(@start) ) AND UNIX_TIMESTAMP( DATE(@end) )
GROUP BY YEAR(FROM_UNIXTIME(yourtable.created_date)), MONTH(FROM_UNIXTIME(yourtable.created_date))
ORDER BY YEAR(FROM_UNIXTIME(yourtable.created_date)), MONTH(FROM_UNIXTIME(yourtable.created_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