How can you display zero in a row using year and month dynamics in an interval?
I am trying to create a query to show count when SET YEAR and MONTH are 12 months apart (year and month are dynamic values - the interval is static value 12 months).
For example: given month = 12 and year = 2013
This will count from 2013-12-01 to 2014-11-30
Another example: set month = 1 and year = 2014
This will count from 2014-01-01 to 2014-12-31
I created this demo but also want to show 0 when the number of rows is not listed in the table, here I used COALESCE
SET @var_year = '2013';
SET @var_month = '12';
SET @from = STR_TO_DATE(CONCAT(@var_year, '/', @var_month, '/01'), '%Y/%m/%d');
SET @to = DATE_ADD(@from, INTERVAL 12 MONTH);
SELECT COALESCE(count(created_at), 0) AS count_all,
year(created_at) as actual_year,
month(created_at)as actual_month
FROM creations
WHERE created_at BETWEEN @from AND @to
GROUP BY month(created_at),year(created_at)
ORDER BY year(created_at),month(created_at) ASC
But, unfortunately, this result came out:
count_all actual_year actual_month
1 2013 12
3 2014 1
2 2014 2
1 2014 3
1 2014 4
1 2014 5
1 2014 6
2 2014 8
1 2014 9
1 2014 10
1 2014 11
I am expecting this result:
count_all actual_year actual_month
1 2013 12
3 2014 1
2 2014 2
1 2014 3
1 2014 4
1 2014 5
1 2014 6
0 2014 7 <------ HERE I WANT 0 WHEN IS NOT IN DB
2 2014 8
1 2014 9
1 2014 10
1 2014 11
I created this live demo using month = 12 and year = 2013 , it does work, but after testing and changing the month or summer values, I got bad bills , so the query does not work correctly, for example this demo using month = 1 and year = 2013 does not work correctly
SET @var_year = '2014';
SET @var_month = '1';
SET @from = STR_TO_DATE(CONCAT(@var_year, '/', @var_month, '/01'), '%Y/%m/%d');
SET @to = DATE_ADD(@from, INTERVAL 12 MONTH);
SELECT IFNULL(counter,0) as counter, literals.y AS actual_year, literals.m AS actual_month
FROM (
SELECT @var_month AS m, @var_year + 0 AS y
UNION SELECT IF((@var_month + 1) % 12, (@var_month + 1) % 12, 12) AS m, @var_year + ((@var_month + 1) DIV 12) AS y
UNION SELECT IF((@var_month + 2) % 12, (@var_month + 2) % 12, 12) AS m, @var_year + ((@var_month + 2) DIV 12) AS y
UNION SELECT IF((@var_month + 3) % 12, (@var_month + 3) % 12, 12) AS m, @var_year + ((@var_month + 3) DIV 12) AS y
UNION SELECT IF((@var_month + 4) % 12, (@var_month + 4) % 12, 12) AS m, @var_year + ((@var_month + 4) DIV 12) AS y
UNION SELECT IF((@var_month + 5) % 12, (@var_month + 5) % 12, 12) AS m, @var_year + ((@var_month + 5) DIV 12) AS y
UNION SELECT IF((@var_month + 6) % 12, (@var_month + 6) % 12, 12) AS m, @var_year + ((@var_month + 6) DIV 12) AS y
UNION SELECT IF((@var_month + 7) % 12, (@var_month + 7) % 12, 12) AS m, @var_year + ((@var_month + 7) DIV 12) AS y
UNION SELECT IF((@var_month + 8) % 12, (@var_month + 8) % 12, 12) AS m, @var_year + ((@var_month + 8) DIV 12) AS y
UNION SELECT IF((@var_month + 9) % 12, (@var_month + 9) % 12, 12) AS m, @var_year + ((@var_month + 9) DIV 12) AS y
UNION SELECT IF((@var_month +10) % 12, (@var_month +10) % 12, 12) AS m, @var_year + ((@var_month +10) DIV 12) AS y
UNION SELECT IF((@var_month +11) % 12, (@var_month +11) % 12, 12) AS m, @var_year + ((@var_month +11) DIV 12) AS y)
AS literals
LEFT JOIN
(SELECT count(*) as counter, year(created_at) y, month(created_at) as m
FROM creations WHERE created_at BETWEEN @from AND @to GROUP BY month(created_at),year(created_at)
ORDER BY year(created_at),month(created_at) ASC
) AS counts
ON literals.m = counts.m AND literals.y = counts.y;
I've spent months searching for information on how to deal with this problem.
If someone can help me, is it not possible to resolve either another trick or request?
source to share
Nice puzzle :)
You can create a table that contains the year and month required for a 12 month interval, and then do an outer join:
SET @var_year = '2013'
SET @var_month = '12'
SELECT @row := @row + (case when right(@row,2) = "12" then 89 else 1 end) as YearMonth
FROM
(select 0 union all select 1 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 6 union all select 7 union all select 8 union all
select 9 union all select 10 union all select 11) t,
(SELECT @row:=CONCAT(@var_year,right(concat('0',(@var_month-1)),2))) r
Gives:
| YearMonth |
|-----------|
| 201312 |
| 201401 |
| 201402 |
| 201403 |
| 201404 |
| 201405 |
| 201406 |
| 201407 |
| 201408 |
| 201409 |
| 201410 |
| 201411 |
So try this (example from 2014 as year and 1 month):
Setting up MySQL 5.5 schema :
CREATE TABLE creations(`id` int, `created_at` date )
;
INSERT INTO creations
(`id`, `created_at`)
VALUES
(1, '2013-12-11'),
(2, '2014-01-11'),
(3, '2014-01-21'),
(4, '2014-01-12'),
(5, '2014-02-22'),
(6, '2014-02-13'),
(7, '2014-03-12'),
(8, '2014-04-23'),
(9, '2014-05-23'),
(10,'2014-06-23'),
(11,'2014-08-23'),
(12,'2014-08-23'),
(13,'2014-09-23'),
(14,'2014-10-23'),
(15,'2014-11-23'),
(16,'2014-12-23')
;
** Request **:
SET @var_year = '2014'
SET @var_month = '1'
SELECT count(created_at) AS count_all,
left(YearMonth,4) as actual_year,
right(YearMonth,2) as actual_month
FROM (
SELECT @row := @row + (case when right(@row,2) = "12" then 89 else 1 end) as YearMonth FROM
(select 0 union all select 1 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 6 union all select 7 union all select 8 union all
select 9 union all select 10 union all select 11) t,
(SELECT @row:=CONCAT(@var_year,right(concat('0',(@var_month-1)),2))) r
) as YearMonthTable
LEFT OUTER JOIN creations ON
CONCAT(year(created_at),right(concat('0',month(created_at)),2)) = YearMonth
GROUP BY YearMonth
ORDER BY YearMonth ASC
Results :
| count_all | actual_year | actual_month |
|-----------|-------------|--------------|
| 3 | 2014 | 01 |
| 2 | 2014 | 02 |
| 1 | 2014 | 03 |
| 1 | 2014 | 04 |
| 1 | 2014 | 05 |
| 1 | 2014 | 06 |
| 0 | 2014 | 07 |
| 2 | 2014 | 08 |
| 1 | 2014 | 09 |
| 1 | 2014 | 10 |
| 1 | 2014 | 11 |
| 1 | 2014 | 12 |
EDITED:
You can also create a table instead of generating it in a subquery every time:
Setting up MySQL 5.5 schema :
CREATE TABLE YearMonthTable(`tblYear` int, `tblMonth` int)
;
INSERT INTO YearMonthTable
(`tblYear`,`tblMonth`)
VALUES
(2013,12),
(2014,1),
(2014,2),
(2014,3),
(2014,4),
(2014,5),
(2014,6),
(2014,7),
(2014,8),
(2014,9),
(2014,10),
(2014,11),
(2014,12),
(2015,1),
(2015,2),
(2015,3),
(2015,4),
(2015,5)
;
Query
SET @var_year = '2014'
SET @var_month = '1'
SET @from = STR_TO_DATE(CONCAT(@var_year, '/', @var_month, '/01'), '%Y/%m/%d')
SET @to = DATE_ADD(DATE_ADD(@from, INTERVAL 12 MONTH), INTERVAL -1 DAY)
SELECT count(created_at) AS count_all,
tblYear as actual_year,
tblMonth as actual_month
FROM YearMonthTable
LEFT OUTER JOIN creations ON year(created_at) = tblYear AND
month(created_at) = tblMonth
WHERE STR_TO_DATE(CONCAT(tblYear, '/', tblMonth, '/01'), '%Y/%m/%d')
BETWEEN @from AND @to
GROUP BY tblMonth, tblYear
ORDER BY tblYear, tblMonth
Results :
| count_all | tblYear | tblMonth |
|-----------|---------|----------|
| 3 | 2014 | 1 |
| 2 | 2014 | 2 |
| 1 | 2014 | 3 |
| 1 | 2014 | 4 |
| 1 | 2014 | 5 |
| 1 | 2014 | 6 |
| 0 | 2014 | 7 |
| 2 | 2014 | 8 |
| 1 | 2014 | 9 |
| 1 | 2014 | 10 |
| 1 | 2014 | 11 |
| 1 | 2014 | 12 |
source to share