CODEWITHSUNDEEP
postgresql
MCM
MCM

Reputation: 141

Joining Queries Into Functioning As One

I have two queries that return different results, and I would like to merge them but I'm still learning how to write in postgreSQ, so I', having some trouble doing this. My queries are:

  1. This one returns a list of all the months until the present one.
SELECT * FROM generate_series('2019-01-01', now(), '1 month')

EXAMPLE:

    generate_series 
------------------------ 
2019-01-01 00:00:00+00
2019-02-01 00:00:00+00
2019-03-01 00:00:00+00
2019-04-01 00:00:00+00
  1. This one returns a count of how many people signed up during that month. But this one is only returning the ones where months are not zero.
SELECT date_trunc('MONTH', (date_signed_up::date)) AS monthly, count(id) AS count FROM customer 
WHERE group_id = 1
GROUP BY monthly
ORDER BY monthly asc

EXAMPLE:

     monthly           | count
-----------------------------------
2019-01-01 00:00:00+00 |   3
2019-02-01 00:00:00+00 |   1
2019-04-01 00:00:00+00 |   1

The expected result or what I need is to have a table like this:

     monthly           | count
-----------------------------------
2019-01-01 00:00:00+00 |   3
2019-02-01 00:00:00+00 |   1
2019-03-01 00:00:00+00 |   0
2019-04-01 00:00:00+00 |   1
2019-05-01 00:00:00+00 |   0

..etc

Upvotes: 0

Views: 18

Answers (1)

Deepstop
Deepstop

Reputation: 3807

SELECT period monthly, count(id) count 
    FROM generate_series('2019-01-01', now(), '1 month') p (period)
        LEFT JOIN customer 
            ON p.period = date_trunc('MONTH', (date_signed_up::date)) AND group_id = 1
    GROUP BY monthly
    ORDER BY monthly asc

Upvotes: 1

Related Questions