search all records for the past 12 months including zero

Question

search all records for the past 12 months including zero

Navigation

#1 by (2 votes)

1

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

Now the count is not working, always returning zero in the count. And what I want is a similar result 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 creative field, created_at , is of 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

asked by anonymous 02.03.2017 / 16:20

1 answer

Database - SQLite How to create a form and authenticate Django user

score 2 · Accepted Answer

Try to get 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;