CODEWITHSUNDEEP
sqloracle-databasegroup-byhaving
Hal
Hal

Reputation: 372

How to use group by correctly in maximum?

I am trying to find the maximum in count using group by

1st code:

 SELECT MAX (COUNT (studid)) AS total,
         unitcode,
         semester,
         TO_CHAR (ofyear, 'yyyy') AS "Year of Offer"
    FROM uni.enrolment
   WHERE TO_CHAR (ofyear, 'YYYY') = '2013'
GROUP BY semester, ofyear, unitcode
ORDER BY total;

Results:

ORA-00937: not a single-group group function 00937. 00000 - "not a single-group group function" *Cause:
*Action: Error at Line: 18 Column: 36

2nd code:

  SELECT unitcode,
         TO_CHAR (ofyear, 'YYYY') AS year,
         semester,
         COUNT (studid) AS student_count
    FROM uni.enrolment
GROUP BY unitcode, ofyear, semester
  HAVING COUNT (studid) = (  SELECT MAX (COUNT (studid))
                               FROM uni.enrolment
                           GROUP BY unitcode, ofyear, semester
                             HAVING TO_CHAR (ofyear, 'YYYY') = '2013')
ORDER BY unitcode;

Results:

╔════════════════════════════════════════════════╗
║ UNITCODE    YEAR     SEMESTER    STUDENT_COUNT ║
╠════════════════════════════════════════════════╣
║ EG2004      2013     1           8             ║
╚════════════════════════════════════════════════╝

When I run the 1st code it gives me an error but the 2nd code works perfect. I don't really understand the error. Why do I have to use a subquery to solve it?

Upvotes: 0

Views: 57

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

The nested aggregation functions are an Oracle-ism. I personally find them rather confusing. But MAX(COUNT( . . . )) is the same as:

select max(cnt)
from (select . . . , count(<something>) as cnt
      from t
      group by . . .
     ) x;

That is, it does two aggregations, one with a group by and one without. The outer one cannot return unaggregated columns. That is why you are getting an error.

I want to note that for your second query, you need to filter on year both inside and outside. So the query might happen to return correct results, but you want to do:

SELECT unitcode, TO_CHAR(ofyear, 'YYYY') AS year, semester,
       COUNT(studid) AS student_count
FROM uni.enrolment
WHERE TO_CHAR(ofyear, 'YYYY') = '2013')
GROUP BY unitcode, TO_CHAR(ofyear, 'YYYY'), semester
HAVING COUNT(studid) = (SELECT MAX (COUNT (studid))
                        FROM uni.enrolment
                        GROUP BY unitcode, ofyear, semester
                        HAVING TO_CHAR(ofyear, 'YYYY') = '2013'
                       )
ORDER BY unitcode;

Notice that I also put the expression in the GROUP BY for year. You want to aggregate by the year, not by the original date.

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35603

You need to calculate the counts before you can determine which of those is the maximum or equal maximum. I suggest using RANK() OVER() or DENSE_RANK() OVER() for the second step; where the result of this equal 1 (when ordered DESC) you have the largest count. In this query the windowing function will be processed after the GROUP BY as part of the SELECT clause (the select clause is NOT the first processed) hence the COUNT() is available as an input for the ranking.

SELECT
        total
      , unitcode
      , semester
      , ofyear
FROM (
      SELECT
              count(studid) AS total
            , unitcode
            , semester
            , to_char(ofyear, 'yyyy') AS "Year of Offer"
            , dense_rank() OVER (ORDER BY count(studid) DESC) AS rnk
      FROM uni.enrolment
      WHERE to_char(ofyear, 'YYYY') = '2013'
      GROUP BY
              semester
            , ofyear
            , unitcode
      ) d
WHERE rnk = 1 --<< this is how we only list the maximum(s)
ORDER BY
        unitcode
      , semester
;

note also you could use EXTRACT(YEAR FROM ofyear) instead of using to_char

Upvotes: 0

Related Questions