Get count of susbcribers for each month in current year even if count is 0-CodePudding

I need to get the count of new subscribers each month of the current year.

DB Structure: Subscriber(subscriber_id, create_timestamp, ...)

Expected result:

date       | count
----------- ------
2021-01-01 | 3
2021-02-01 | 12
2021-03-01 | 0
2021-04-01 | 8
2021-05-01 | 0

I wrote the following query:

SELECT
       DATE_TRUNC('month',create_timestamp)
         AS  create_timestamp,
       COUNT(subscriber_id) AS count
FROM subscriber
GROUP BY DATE_TRUNC('month',create_timestamp);

Which works but does not include months where the count is 0. It's only returning the ones that are existing in the table. Like:

"2021-09-01 00:00:00"   3
"2021-08-01 00:00:00"   9

CodePudding user response：

First subquery is used for retrieving year wise each month row then LEFT JOIN with another subquery which is used to retrieve month wise total_count. COALESCE() is used for replacing NULL value to 0.

-- PostgreSQL (v11)
SELECT t.cdate
     , COALESCE(p.total_count, 0) total_count
FROM (select generate_series('2021-01-01'::timestamp, '2021-12-15', '1 month') as cdate) t
LEFT JOIN (SELECT DATE_TRUNC('month',create_timestamp) create_timestamp
                , SUM(subscriber_id) total_count
           FROM subscriber
           GROUP BY DATE_TRUNC('month',create_timestamp)) p
       ON t.cdate = p.create_timestamp

Please check from url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=20dcf6c1784ed0d9c5772f2487bcc221

CodePudding user response：

get the count of new subscribers each month of the current year

SELECT month::date, COALESCE(s.count, 0) AS count
FROM   generate_series(date_trunc('year', LOCALTIMESTAMP)
                     , date_trunc('year', LOCALTIMESTAMP)   interval '11 month'
                     , interval '1 month') m(month)
LEFT   JOIN (
   SELECT date_trunc('month', create_timestamp) AS month
        , count(*) AS count
   FROM   subscriber
   GROUP  BY 1
   ) s USING (month);

db<>fiddle here

That's assuming every row is a "new subscriber". So count(*) is simplest and fastest.

See: