1

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
4

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:

0
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 
0

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;
  • You can't have filter (where status = 'paid') because status is not in the group by list. – ypercubeᵀᴹ Dec 16 '16 at 8:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.