PostgreSQL – How to Add a Summary Count to a Query

countpostgresqlwindow functions

I am using Postgres 9.4 and I have a query that looks like the following:

SELECT attendees.event_id, attendees.user_id, users.tags
FROM users, attendees
WHERE 'crystal:promo' = ANY(users.tags)
AND users.id = attendees.user_id
AND attendees.status = 'paid' 
GROUP BY attendees.event_id, attendees.user_id, users.tags
ORDER BY attendees.event_id desc;

Result:

 event_id | user_id | tags 
----------+---------+------
 1        | 2       | ...
 1        | 4       | ...
 1        | 7       | ...

I'd like to add a summary column for each row that is the total number of people per event independent of user tags such that the result would be something like:

select count(*) from attendees where status='paid' group by event_id

and joined with the above results (assuming 11 paid):

 event_id | user_id | tags | total_event_paid
----------+---------+------+------------------
 1        | 2       | ...  | 11
 1        | 4       | ...  | 11
 1        | 7       | ...  | 11

Best Answer

You can just replace attendees with a subquery that pre-selects "paid" rows and add the count without aggregating - with a window function:

SELECT DISTINCT a.event_id, a.user_id, u.tags, a.total_event_paid
FROM   users u
JOIN  (
   SELECT *, count(*) OVER (PARTITION BY event_id) AS total_event_paid
   FROM   attendees
   WHERE  status = 'paid'
   ) a ON u.id = a.user_id
WHERE  'crystal:promo' = ANY(u.tags)
ORDER  BY a.event_id DESC;

DISTINCT (or GROUP BY) in the outer SELECT may or may not be needed.

Related:

Related Question