# 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?

+3

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
```

```

``````| 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')

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
```

```
``````| 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 |
```

```
+4

source

All Articles