Sum results of a few queries and then find top 5 in SQL

Question

I have 3 queries:

table: pageview
SELECT event_id, count(*) AS pageviews 
FROM pageview 
GROUP BY event_id
ORDER BY pageviews DESC, rand()
LIMIT 1000

table: upvote
SELECT event_id, count(*) AS upvotes 
FROM upvote
GROUP BY event_id
ORDER BY upvotes DESC, rand()
LIMIT 1000

table: attending
SELECT event_id, count(*) AS attendants
FROM attending
GROUP BY event_id
ORDER BY attendants DESC, rand()
LIMIT 1000

I'd like to combine the event_ids of all 3 queries ordered by amount and then choose the top 5. How do I do that?

EDIT: HERE IS WHAT I DID TO MAKE IT HAPPEN:

SELECT event_id, sum(amount) AS total
FROM (
(SELECT event_id, count(*) AS amount
FROM   pageview 
GROUP  BY event_id
ORDER  BY amount DESC, rand()
LIMIT  1000)

UNION ALL
(SELECT event_id, count(*) as amount
FROM   upvote
GROUP  BY event_id
ORDER  BY amount DESC, rand()
LIMIT  1000)

UNION ALL
(SELECT event_id, count(*) as amount
FROM   attending
GROUP  BY event_id
ORDER  BY amount DESC, rand()
LIMIT  1000)
) x
GROUP  BY 1
ORDER  BY  sum(amount) DESC
LIMIT  5;

How do you determine the top 5 of 3 distinct groups? Each could theoretically have 5 unique ID's not in the other lists. Do you sum them? — MPelletier, Dec 16 '11 at 01:34
Define "combine". Add up the counts or just UNION the query results? Define "amount". Each count separately and compare `pageviews` to `upvotes` to `attendants`? or add them for each `event_id`? — Erwin Brandstetter, Dec 16 '11 at 01:52

Erwin Brandstetter · Accepted Answer · 2021-04-28T12:50:21.483

To UNION the resulting rows of all three queries and then pick the 5 rows with the highest amount:

(SELECT event_id, count(*) AS amount
FROM   pageview 
GROUP  BY event_id
ORDER  BY pageviews DESC, rand()
LIMIT  1000)

UNION ALL
(SELECT event_id, count(*)
FROM   upvote
GROUP  BY event_id
ORDER  BY upvotes DESC, rand()
LIMIT  1000)

UNION ALL
(SELECT event_id, count(*)
FROM   attending
GROUP  BY event_id
ORDER  BY attendants DESC, rand()
LIMIT  1000)

ORDER  BY 2 DESC
LIMIT  5;

The manual:

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT.

UNION ALL to keep duplicates.

To add the counts for every event_id:

SELECT event_id, sum(amount) AS total
FROM (
   (SELECT event_id, count(*) AS amount
    FROM   pageview 
    GROUP  BY event_id
    ORDER  BY pageviews DESC, rand()
    LIMIT  1000)
    
    UNION ALL
    (SELECT event_id, count(*)
    FROM   upvote
    GROUP  BY event_id
    ORDER  BY upvotes DESC, rand()
    LIMIT  1000)
    
    UNION ALL
    (SELECT event_id, count(*)
    FROM   attending
    GROUP  BY event_id
    ORDER  BY attendants DESC, rand()
    LIMIT  1000)
    ) x
GROUP  BY 1
ORDER  BY sum(amount) DESC
LIMIT  5;

The tricky part here is that not every event_id will be present in all three base queries. So take care that a JOIN does not lose rows completely and additions don't turn out NULL.

Use UNION ALL, not UNION. You don't want to remove identical rows, you want to add them up.

x is a table alias and shorthand for AS x. It is required for for a subquery to have a name. Can be any other name here.

The SOL feature FULL OUTER JOIN is not implemented in MySQL (last time I checked), so you have to make do with UNION. FULL OUTER JOIN would join all three base queries without losing rows.

Answer to follow-up question

SELECT event_id, sum(amount) AS total
FROM (
   (SELECT event_id, count(*) / 100 AS amount
    FROM   pageview ... )
    
    UNION ALL
    (SELECT event_id, count(*) * 5 
    FROM   upvote ... )
    
    UNION ALL
    (SELECT event_id, count(*) * 10
    FROM   attending ... )
    ) x
GROUP  BY 1
ORDER  BY  sum(amount) DESC
LIMIT  5;

Or, to use the base counts in multiple ways:

SELECT event_id
      ,sum(CASE source
              WHEN 'p' THEN amount / 100
              WHEN 'u' THEN amount * 5
              WHEN 'a' THEN amount * 10
              ELSE 0
           END)  AS total
FROM (
   (SELECT event_id, 'p'::text AS source, count(*) AS amount
    FROM   pageview ... )
    
    UNION ALL
    (SELECT event_id, 'u'::text, count(*)
    FROM   upvote ... )
    
    UNION ALL
    (SELECT event_id, 'a'::text, count(*)
    FROM   attending ... )
    ) x
GROUP  BY 1
ORDER  BY 2 DESC
LIMIT  5;

Brandstetter, I believe the answer is the second one - say that `event_id=1` will correspond to 3 pageviews, 2 upvotes, and 1 attendant, I'd like the final amount associated with it to be 6. Then for exmaple `event_id=2` has 0 pageviews, 14 upvotes, and 0 attendees, the final value will be 14. Once I sum them together, I would like to take the top 5 results. — Andrew Samuelsen, Dec 16 '11 at 03:47
follow on question: say I want to calculate the final value for `event_id` as something like this ((pageviews/100) + upvotes*5 + attendants*10). How would I do this? — Andrew Samuelsen, Dec 16 '11 at 04:26

Sum results of a few queries and then find top 5 in SQL

1 Answers1

Answer to follow-up question

Linked