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

user_15751265 0
Viewed 42

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;
amazon-redshift amazon-rds postgresql sql
1 Answers

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
user_11440350
Related