CODEWITHSUNDEEP
postgresql
Theodore R. Smith
Theodore R. Smith

Reputation: 23231

Combine two group bys?

I have two queries:

SELECT to_char(pubdate, 'YYYY-MM') pub_month, COUNT(*) total 
FROM books 
GROUP BY pub_month;

and

SELECT to_char(pubdate, 'YYYY-MM') pub_month, COUNT(*) total_bought 
FROM books 
WHERE purchased=true 
GROUP BY pub_month;

Is there any way to combine them so that the result is

+-----------+-------+--------------
| pub_month | total | total_bought 
+-----------+-------+--------------
|   2018-10 |     5 |            2
|   2018-09 |    10 |            7

Upvotes: 1

Views: 54

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

You could use FILTER clause:

The filter clause extends aggregate functions (sum, avg, count, …) by an additional where clause. The result of the aggregate is built from only the rows that satisfy the additional where clause too.

SELECT to_char(pubdate, 'YYYY-MM') pub_month
    , COUNT(*) FILTER (WHERE purchased) total_bought
    , COUNT(*) total 
FROM books 
GROUP BY pub_month;

or conditional aggregation:

SELECT to_char(pubdate, 'YYYY-MM') pub_month
    , SUM(CASE WHEN purchased THEN 1 ELSE 0 END) total_bought
    , COUNT(*) total 
FROM books 
GROUP BY pub_month;

Additional note: WHERE purchased=true <=> WHERE purchased

Upvotes: 4

Grzegorz Grabek
Grzegorz Grabek

Reputation: 980

You can make it in one sql query.

SELECT to_char(pubdate, 'YYYY-MM') pub_month, COUNT(*) total , 
       sum(case when purchased=true then 1 else 0 end) total_bought 
FROM books 
GROUP BY pub_month;

Upvotes: 2

Cade Roux
Cade Roux

Reputation: 89661

SELECT to_char(pubdate, 'YYYY-MM') pub_month
           , COUNT(*) total
           , SUM(CASE WHEN purchased=true THEN 1 ELSE 0 END) total_bought 
FROM books 
GROUP BY pub_month;

Upvotes: 1

Related Questions