Multiple aggregate functions in SQL query

Question

For this example I got 3 simple tables (Page, Subs and Followers):

For each page I need to know how many subs and followers it has. My result is supposed to look like this:

I tried using the COUNT function in combination with a GROUP BY like this:

SELECT p.ID, COUNT(s.UID) AS SubCount, COUNT(f.UID) AS FollowCount 
FROM page p, subs s, followers f 
WHERE p.ID = s.ID AND p.ID = f.ID AND s.ID = f.ID 
GROUP BY p.ID

Obviously this statement returns a wrong result.

My other attempt was using two different SELECT statements and then combining the two subresults into one table.

SELECT p.ID, COUNT(s.UID) AS SubCount FROM page p, subs s WHERE p.ID = s.ID GROUP BY p.ID

and

SELECT p.ID, COUNT(f.UID) AS FollowCount FROM page p, follow f WHERE p.ID = f.ID GROUP BY p.ID

I feel like there has to be a simpler / shorter way of doing it but I'm too unexperienced to find it.

Gordon Linoff · Accepted Answer · 2019-03-02 23:45:35Z

3

Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.

Next, learn what COUNT() does. It counts the number of non-NULL values. So, your expressions are going to return the same value -- because f.UID and s.UID are never NULL (due to the JOIN conditions).

The issue is that the different dimensions are multiplying the amounts. A simple fix is to use COUNT(DISTINCT):

SELECT p.ID, COUNT(DISTINCT s.UID) AS SubCount, COUNT(DISTINCT f.UID) AS FollowCount 
FROM page p JOIN
     subs s
     ON p.ID = s.ID JOIN
     followers f 
     ON s.ID = f.ID 
GROUP BY p.ID;

The inner joins are equivalent to the original query. You probably want left joins so you can get counts of zero:

SELECT p.ID, COUNT(DISTINCT s.UID) AS SubCount, COUNT(DISTINCT f.UID) AS FollowCount 
FROM page p LEFT JOIN
     subs s
     ON p.ID = s.ID LEFT JOIN
     followers f 
     ON p.ID = f.ID 
GROUP BY p.ID;

edited Mar 2, 2019 at 23:45

answered Mar 2, 2019 at 22:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rolf Wolf Over a year ago

Your solution works as long as a page doesn't have 0 followers or subs. I removed the last entry of my subs table so that the Page with ID 2 has 0 Subs and 4 Followers. Now ID 2 is not shown at all. I replaced all joins with left joins. Now ID 2 is shown with the values 0 Subs and 0 Followers (although its 4 Followers). Do you know what I did wrong there?

Gordon Linoff Over a year ago

@RolfWolf . . . The second join should be to p.id, not s.id.

Lukas Eder Over a year ago

The overhead of the cartesian product produced between subs and followers this way might be significant if the numbers are high. I think a solution with correlated subqueries is better in this case...

Gordon Linoff Over a year ago

@LukasEder . . . Or by aggregating before joining. My answer was directed to the way the OP originally framed the query.

karel · Accepted Answer · 2019-03-03 07:10:11Z

1

Scalar subquery should work in this case.

SELECT p.id,
       (SELECT Count(s_uid)
        FROM   subs s1
        WHERE  s1.s_id = p.id) AS cnt_subs,
       (SELECT Count(f_uid)
        FROM   followers f1
        WHERE  f1.f_id = p.id) AS cnt_fol
FROM   page p
GROUP  BY p.id;

edited Mar 3, 2019 at 7:10

karel

5,97562 gold badges58 silver badges60 bronze badges

answered Mar 3, 2019 at 2:50

Sunil Kumar

913 bronze badges

Collectives™ on Stack Overflow

Multiple aggregate functions in SQL query

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related