retrieve all records from the last 12 months including zero

Asked

Viewed 603 times

1

I have my table bud_quotations and I want to retrieve all the records that have been entered in the last 12 months, including the months that have no record.

Now the count is not working, always returning zero in the count. And what I want is a result similar to this:

+------+----+-----------------------------+
| y    | m  | Cbud_quotations.created_at) |
+------+----+-----------------------------+
| 2016 |  4 |                 5595        |
| 2016 |  5 |                 4431        |
| 2016 |  6 |                 3299        |
| 2016 |  7 |                  429        |
| 2016 |  8 |                    0        |
| 2016 |  9 |                 3698        |
| 2016 | 10 |                 6208        |
| 2016 | 11 |                 5142        |
| 2016 | 12 |                 1196        |
| 2017 |  1 |                   10        |
| 2017 |  2 |                    0        |
| 2017 |  3 |                    0        |
+------+----+-----------------------------+

My field of creation, created_at, is the type DATETIME. My query is as follows::

 SELECT y, m, Count(bud_quotations.created_at)
 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 bud_quotations
   ON ym.y = YEAR(FROM_UNIXTIME(bud_quotations.created_at))
      AND ym.m = MONTH(FROM_UNIXTIME(bud_quotations.created_at))
 WHERE
   (y=YEAR(CURDATE()) AND m<=MONTH(CURDATE()))
   OR
   (y<YEAR(CURDATE()) AND m>MONTH(CURDATE()))
 GROUP BY y, m;
mysql sql

1 answer

2


Try taking the FROM_UNIXTIME:

SELECT y, m, Count(bud_quotations.created_at)
 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 bud_quotations
   ON ym.y = YEAR(bud_quotations.created_at)
      AND ym.m = MONTH(bud_quotations.created_at)
 WHERE
   (y=YEAR(CURDATE()) AND m<=MONTH(CURDATE()))
   OR
   (y<YEAR(CURDATE()) AND m>MONTH(CURDATE()))
 GROUP BY y, m;

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.