postgresql select column twice with different creteria

user_7519048 0
Viewed 59

With PostgreSQL: I have a table named "people" with 4 columns: id, user_id, date_id, and status (which can have two values: "showed" "signup")

I'm looking to display 3 columns with a select query:

SELECT date_id, count(DISTINCT p.user_id) as people_attending, count(p.id) as people_registered
FROM people p
where (p.status::TEXT = 'showed') + MISSING PART HERE??
GROUP BY date_id
ORDER BY "date_id" ASC

Looking to get this:

date_id || people_attending || people_registered 
12      || 100              || 230
34      || 10               || 12

Basically I'm trying to select one column (people_attending) where p.status = "showed" and then a third column (people_registered) where p.status = all cases. My first two columns are fine with the query above, however, the third column is an issue.

I'm having a difficult time finding a solution, reading Selecting same column twice from a single table but with different conditions and trying to do a self join.

Thanks for your help, MM.

inner-join postgresql
2 Answers

According to PostgreSQL documentation:

count ( "any" ) → bigint

Computes the number of input rows in which the input value is not null.

So you can count persons on a condition:

SELECT 
  date_id, 
  count(DISTINCT CASE WHEN status = 'showed' THEN p.user_id ELSE NULL END) as people_attending,
  count(DISTINCT CASE WHEN status = 'signup' THEN p.user_id ELSE NULL END) as people_registered
FROM 
  people p
GROUP BY 
  date_id
ORDER BY
  "date_id" ASC

You can use a little more compact form with FILTER (see aggregate expresions )

SELECT 
  date_id, 
  count(DISTINCT p.user_id) FILTER (WHERE status = 'showed') as people_attending,
  count(DISTINCT p.user_id) FILTER (WHERE status = 'signup') as people_registered
FROM 
  people p
GROUP BY 
  date_id
ORDER BY
  "date_id" ASC
user_80536550

If you must use one query to get that exact same result set, you can try this query:

select a00.date_id, a01.c as showed, a00.c as all_participants
  from
   (
       select date_id, 'all', count(1) as c from people group by date_id
   ) a00 left outer join
   (
       select date_id, 'showed', count(1) as c from people where status = 'showed' group by date_id
   ) a01 on (a00.date_id = a01.date_id)

Having said that, I would consider (if possible at all) just to get raw data into your application and do the calculations there. It will be easier to understand in five years when you come back to do some sort of bug fixing/addition to your project.

user_676060
Related