How to add a summary count to this query

Question

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

Answer 1

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:

• Best way to get result count before LIMIT was applied

Answer 2

WITH tbl_count as (select event_id, count(*) as rec_count from attendees 
                    where status='paid' group by event_id),
tbl_main as (
SELECT attendees.event_id, attendees.user_id, users.tags
FROM users 
INNER JOIN attendees ON users.id = attendees.user_id
WHERE 
    'crystal:promo' = ANY(users.tags)
    and 
attendees.status = 'paid' 
GROUP BY attendees.event_id, attendees.user_id, users.tags ORDER BY attendees.event_id desc)

SELECT 
    tbl_main.event_id, 
    tbl_main.user_id, 
    tbl_main.tags,
    tbl_count.rec_count
FROM tbl_main
LEFT JOIN tbl_count ON tbl_count.event_id = tbl_main.event_id 

Answer 3

Starting with 9.4 you could use window function with FILTER. Something like:

SELECT attendees.event_id, attendees.user_id, users.tags
,count(*) filter (where status = 'paid') over (partition by event_id) users_paid
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;