Geek Answers Handbook

Group by month, return 0 if the record is not found

I want to get records from a database table in the last 12 months. Here is what I have tried so far.

SELECT COUNT(s.id), date_part('month', s.viewed_at) month_number
    FROM statistics_maps_view as s
    INNER JOIN maps as m
    ON s.maps_id=m.id Where m.users_id = $users_id group by month_number ORDER BY month_number DESC LIMIT 12

I know that he will group months of records. but is there a way to add Count = 0if there is no entry for a specific month?

+4
source share
1 answer

The offer group bywill not create records in which there is no data, as you saw. What you can do is left joinall this result with another set of results that contains all the records you need, for example, one of them that you dynamically generate with generate_series:

SELECT    generate_series AS month_number, cnt
FROM      GENERATE_SERIES(1,12) g
LEFT JOIN (SELECT     COUNT(s.id) AS cnt, 
                      DATE_PART('month', s.viewed_at) AS month_number
           FROM       statistics_maps_view s
           INNER JOIN maps m ON s.maps_id = m.id 
           WHERE      m.users_id = $users_id 
           GROUP BY   month_number) s ON g.generate_series = s.month_number
ORDER BY  1 ASC
+3
source

More articles:


All Articles