CODEWITHSUNDEEP
sql
FlamePrinz
FlamePrinz

Reputation: 490

How do I get the full row of what is essentially a nested aggregate function in SQL?

I have a table of events, and a table of participation in said events. I want to get the count of the people who participated at every fundraising event.

This is done successfully with:

SELECT Participate.eid, COUNT(Participate.pid) AS numpeople
FROM Participate
WHERE Participate.eid IN(
    SELECT Event.eid
    FROM Event
    WHERE Event.type = 'fundraising'
)
GROUP BY Participate.eid

This outputs a result of:

eid      numpeople
16           8
 6           7
 3          10
 5           6

What I want now is to get the maximum amount of people who attended a specific event. First of all, I am required to not use any Views, and this is the only way I have been able to do it. I have also tried with derived tables but with no success.

I essentially want a result of:

eid     numpeople
 3         10

I tried doing this with derived tables, but it does not work because I'm forced to have a GROUP BY statement:

SELECT Temp1.eid, MAX(Temp1.numpeople) AS numpeople
FROM (
    SELECT Participate.eid, COUNT(Participate.pid) AS numpeople
    FROM Participate
    WHERE Participate.eid IN(
        SELECT Event.eid
        FROM Event
        WHERE Event.type = 'fundraising'
    )
    GROUP BY Participate.eid
) AS Temp1;

This is the closest thing I have gotten, but obviously, the 'eid' is not present in the row:

SELECT MAX(Temp1.numpeople) AS numpeople
FROM (
    SELECT Participate.eid, COUNT(Participate.pid) AS numpeople
    FROM Participate
    WHERE Participate.eid IN(
        SELECT Event.eid
        FROM Event
        WHERE Event.type = 'fundraising'
    )
    GROUP BY Participate.eid
) AS Temp1;

Which has output:

numpeople
   10

Upvotes: 0

Views: 23

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Use order by and limit the result to one row:

SELECT p.eid, COUNT(p.pid) AS numpeople
FROM Participate p
WHERE p.eid IN (SELECT e.eid
                FROM Event e
                WHERE e.type = 'fundraising'
               )
GROUP BY p.eid
ORDER BY numpeople DESC
FETCH FIRST 1 ROW ONLY;

Not all databases support FETCH FIRST, so you might need to use SELECT TOP or LIMIT.

Upvotes: 1

Related Questions