CODEWITHSUNDEEP
sqldatabasepostgresqlpostgresql-9.5
Boopathi Saravanan
Boopathi Saravanan

Reputation: 133

Postgresql query to get count per months within one year

In MySQL 

SELECT y, m, Count(users.created_date)
     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_date))
          AND ym.m = MONTH(FROM_UNIXTIME(users.created_date))
     WHERE
       (y=YEAR(CURDATE()) AND m<=MONTH(CURDATE()))
       OR
       (y<YEAR(CURDATE()) AND m>MONTH(CURDATE()))
     GROUP BY y, m;

Mysql Output

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

** IN POSTGRESQL QUERY**

SELECT to_char(created_date, 'MM'),
       count(id)
FROM users
WHERE created_date >
      date_trunc('month', CURRENT_DATE) - INTERVAL '1 year'
GROUP BY 1

POSTGRESQL OUTPUT postgresql output

May I know how to get postgresql output as like mysql output. Need to get results for current month within one year data.

Upvotes: 2

Views: 4179

Answers (1)

Julia Leder
Julia Leder

Reputation: 816

I'm assuming what you're looking for is the rows where count is 0. If that is the case you can use generate_series and a left join on your table with data:

    SELECT to_char(i, 'YY'), to_char(i, 'MM'),
        count(id)
    FROM generate_series(now() - INTERVAL '1 year', now(), '1 month') as i
    left join users on (to_char(i, 'YY') = to_char(created_date, 'YY') 
                        and to_char(i, 'MM') = to_char(created_date, 'MM') and login_type = 1)
    GROUP BY 1,2;

Upvotes: 3

Related Questions