SQL nested aggregate functions MAX(COUNT(*))

Question

I'm trying to select max(count of rows). Here is my 2 variants of SELECT

SELECT MAX(COUNT_OF_ENROLEES_BY_SPEC) FROM
(SELECT D.SPECCODE, COUNT(D.ENROLEECODE) AS COUNT_OF_ENROLEES_BY_SPEC
 FROM DECLARER D
 GROUP BY D.SPECCODE
);

SELECT S.NAME, MAX(D.ENROLEECODE)
  FROM SPECIALIZATION S
    CROSS JOIN DECLARER D WHERE S.SPECCODE = D.SPECCODE
  GROUP BY S.NAME
  HAVING MAX(D.ENROLEECODE) = 
  ( SELECT MAX(COUNT_OF_ENROLEES_BY_SPEC) FROM
      ( SELECT D.SPECCODE, COUNT(D.ENROLEECODE) AS COUNT_OF_ENROLEES_BY_SPEC
        FROM DECLARER D
        GROUP BY D.SPECCODE
      )
  );

The first one is working OK, but I want to rewrite it using "HAVING" like in my second variant and add there one more column. But now 2nd variant don't output any data in results, just empty columns. How can I fix it ? Thank YOU!)

You want the maximum code equal a maximum number of codes? That doesn't look reasonable. — Thorsten Kettner
– Thorsten Kettner, Commented Apr 7, 2014 at 14:52
I want to count number of rows(declarations) grouped by specialization code, than take the most popular specialization. Sorry for my English;( Is it clear now ? — Valery Bugakov
– Valery Bugakov, Commented Apr 7, 2014 at 14:59

ThinkJet · Accepted Answer · 2014-04-07 15:40:53Z

2

This query based on description given in comments and some suggestions, so it may be wrong:

select                   -- 4. Join selected codes with specializations
  S.Name,
  selected_codes.spec_code,
  selected_codes.count_of_enrolees_by_spec   
from 
  specialization S,
  ( 
    select               -- 3. Filter records with maximum popularity only
      spec_code, 
      count_of_enrolees_by_spec
    from (
      select             -- 2. Count maximum popularity in separate column
        spec_code, 
        count_of_enrolees_by_spec,
        max(count_of_enrolees_by_spec) over (partition by null) max_count
      from (
        SELECT           -- 1. Get list of declarations and count popularity
          D.SPECCODE           AS SPEC_CODE, 
          COUNT(D.ENROLEECODE) AS COUNT_OF_ENROLEES_BY_SPEC
        FROM DECLARER D
        GROUP BY D.SPECCODE
      )
    )
    where count_of_enrolees_by_spec = max_count
  )
    selected_codes
where 
  S.SPECCODE = selected_codes.spec_code

Also query not tested and some syntax errors are possible.

answered Apr 7, 2014 at 15:40

ThinkJet

6,76526 silver badges36 bronze badges

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

6 Comments

Valery Bugakov Over a year ago

Thank you, it works witout mistakes! Can you, please, explain a little bit this part - "over (partition by null)"?

ThinkJet Over a year ago

To use analytic functions you need to specify partitions in PARTITION BY clause to select group of rows like in GROUP BY. If you need only one group with all rows of dataset, it's useful to specify some constant expression. E.g. 1, 'X' and so on. Null also works. Personally, I prefer null for more code readability because it don't raise a questions like "Why choosed 1 and not 0?" and so on.

Valery Bugakov Over a year ago

Thank You ! Great explanation. Can we use in this select GROUP BY expression instead of OVER (PARTITION BY NULL) to get same result ? I read about these expressions but I don't understand why I got error if I use max(count_of_enrolees_by_spec) and then trying to GROUP BY spec_code and count_of_enrolees_by_spec.

ThinkJet Over a year ago

GROUP BY over full dataset (like GROUP BY 'x') is equivalent to just select max(COUNT_OF_ENROLEES_BY_SPEC) without any grouping. Each group must be identified uniquely and divides source record set to distinct groups without intersections. Therefore with one group you can't distinguish between different SPECCODEs and just got single value of maximum record count without ability to link it back to DECLARER or SPECIALIZATION tables.

ThinkJet Over a year ago

From other side if you do GROUP BY over first (1.) query with some partitioning then you don't give maximum value.

|

Collectives™ on Stack Overflow

SQL nested aggregate functions MAX(COUNT(*))

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related