wondering why aggregation does not work:Ii want to write a sql query to use aggregation and having c-CodePudding

hi I have 2 tables want to use aggregation and having cluse. can u plz check my query? does not work

SELECT
    registration.Staff_ID,
    staff.FirstName,
    staff.LastName,
    (sum(registration.RegFeeAmntPaid)) AS The_Best_Seller
FROM registration,staff
WHERE registration.Staff_ID = staff.Staff_ID
GROUP BY Staff_ID
HAVING MAX(SUM(registration.RegFeeAmntPaid)) = SUM(registration.RegFeeAmntPaid);

CodePudding user response：

The nested aggregation MAX(SUM(registration.RegFeeAmntPaid)) is the problem: Nested aggregations are not allowed in SQL: After the first aggregation, there is only one value. Thus, aggregating again does not make sense. (In Oracle, under SELECT of a GROUP BY query, one can use a nested aggregation to get a single value, but this is not standard.)

Furthermore, in most systems, attributes printed under SELECT without aggregation must appear under GROUP BY. (In Standard SQL and PostgreSQL, attributes that are functionally determined by GROUP BY attributes are ok. But for instance in Oracle, they must be explicitly mentioned under GROUP BY, even if that does not actually change the groups.)

Furthermore, in the GROUP BY clause, Staff_ID is missing a table/tupe variable name (It is not clear, whether you mean registration.Staff_ID or staff.Staff_ID. I know that it does not matter, but an SQL system does not know.)

There are several solutions to maximize a sum. One is this:

SELECT
    registration.Staff_ID,
    staff.FirstName,
    staff.LastName,
    (sum(registration.RegFeeAmntPaid)) AS The_Best_Seller
FROM registration,staff
WHERE registration.Staff_ID = staff.Staff_ID
GROUP BY registration.Staff_ID,
         staff.FirstName, staff.LastName
HAVING SUM(registration.RegFeeAmntPaid) >= ALL
    (SELECT SUM(s.RegFeeAmntPaid)
     FROM   registration s
     GROUP BY s.staff_id)

Another solution would be to use WITH clause to define an auxiliary table that contains the sum for each staff_id. Then you could use the MAX function. This is also possible without WITH clause, but then one needs a subquery under FROM (which also gives an intermediate table). One must put the two aggregations into different SELECT clauses.