postgresql - select 4 columns, 1 of the columns requires filtering

Question

Beginner PostgreSQL user here, don't have a great grasp of JOIN or CASE yet.

What's the best way to filter in my select statement so that I can get the following output:

signup_month
total_signups
business_signups
total_users

example table:

date	signup_email	tier	users
2014-05-02	personal	trial	1
2014-05-04	personal	subscription	1
2014-05-12	personal	subscription	2
2014-05-25	business	trial	1
2014-06-02	personal	subscription	1
2014-06-20	business	subscription	10
2014-06-30	business	trial	5

Sample output:

signup_month	total_signups	business_signups	total_users
may	4	1	5
june	3	2	16

Here's a basic query that just outputs 3 columns (signup_month, total_signups and total_users). I'm having trouble adding a 4th column that just counts business_signups from the signup_email column since it requires counting only values that = 'business'

SELECT DATE_PART(month,signup_date) AS signup_month,
       COUNT(signup_email) AS total_signups,
       SUM(users) AS total_users

FROM signups
GROUP BY signup_month;

Gordon Linoff · Accepted Answer · 2021-07-03 20:23:55Z

3

You want conditional aggregation, which in Postgres uses filter.

However, you probably want to include the year with the month, so I recommend date_trunc():

SELECT DATE_TRUNC('month', signup_date) AS signup_month,
       COUNT(*) AS total_signups,
       COUNT(*) FILTER (WHERE signup_email = 'business') as business_signups,
       SUM(users) AS total_users
FROM signups
GROUP BY signup_month

answered Jul 3, 2021 at 20:23

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql - select 4 columns, 1 of the columns requires filtering

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related