ORACLE 12c - "not a single-group group function"

I have a table o employees that contains names, date of employment and some more information.

I want to check which year the most employees were employed.

I write a query which count employment for each year:

SELECT EXTRACT (YEAR FROM e1.empl_date) AS YEAR, COUNT(e1.id_empl) AS EMPL_NUMBER
            FROM employees e1
            GROUP BY EXTRACT (YEAR FROM e1.empl_date);

And result of this query are tuples:

YEAR   | EMPL_NUMBER
1993   | 3
1997   | 2

and so on...

And now I want to get max of EMPL_NUMBER:

SELECT YEAR, MAX(EMPL_NUMBER)
FROM (SELECT EXTRACT (YEAR FROM e1.empl_date) AS YEAR, COUNT(e1.id_empl) AS EMPL_NUMBER
        FROM employees e1
        GROUP BY EXTRACT (YEAR FROM e1.empl_date));

And then I get an error:

ORA-00937: not a single-group group function

I don't understand why I get an error because subquery returns tuple with 2 columns.

3 answers

  • answered 2017-06-17 18:17 scaisEdge

    You are using an aggregation function on the select result so If you need all the distinct YEAR you ust group by

      SELECT T.YEAR, MAX(T.EMPL_NUMBER)
      FROM (
          SELECT EXTRACT (YEAR FROM e1.empl_date) AS YEAR, COUNT(e1.id_empl) AS EMPL_NUMBER
          FROM employees e1
          GROUP BY EXTRACT (YEAR FROM e1.empl_date)
      ) T 
      GROUP BY T.YEAR ;
    

    Otherwise if you need the year of the MAX(EMPL_NUMBER) you could

      SELECT T.YEAR, T.EMPL_NUMBER
      FROM  (
          SELECT EXTRACT (YEAR FROM e1.empl_date) AS YEAR, COUNT(e1.id_empl) AS EMPL_NUMBER
          FROM employees e1
          GROUP BY EXTRACT (YEAR FROM e1.empl_date)
      ) T
      WHERE (T.EMPL_NUMBER)  IN (SELECT  MAX(EMPL_NUMBER)
         FROM (
          SELECT EXTRACT (YEAR FROM e1.empl_date) AS YEAR, COUNT(e1.id_empl) AS EMPL_NUMBER
          FROM employees e1
          GROUP BY EXTRACT (YEAR FROM e1.empl_date)
      ) T1  )
    

  • answered 2017-06-17 18:17 Gordon Linoff

    In Oracle 12C, you can do:

    SELECT EXTRACT(YEAR FROM e1.empl_date) AS YEAR, COUNT(e1.id_empl) AS EMPL_NUMBER
    FROM employees e1
    GROUP BY EXTRACT(YEAR FROM e1.empl_date)
    ORDER BY COUNT(e1.id_empl) DESC
    FETCH FIRST 1 ROW ONLY;
    

  • answered 2017-06-17 18:17 mathguy

    One way to do this is to use an aggregate query as you were doing already, and then to use aggregate functions to their full extent. For example, using the FIRST/LAST function (and using the SCOTT schema, EMP table for illustration):

    select min(extract(year from hiredate)) keep (dense_rank last order by count(empno)) as yr,
           max(count(empno)) as emp_count
    from   emp
    group by extract(year from hiredate)
    ;
    
    
      YR  EMP_COUNT
    ----  ---------
    1981         10
    

    There are two problems with this solution. First, many developers (including many experienced ones) seem unaware of the FIRST/LAST function, or otherwise unwilling to use it. The other, more serious problem is that in this problem it is possible that there are several years with the same, highest number of hires. The problem requirement must be more detailed than in the Original Post. What is the desired output when there are ties for first place?

    The query above returns the earliest of all the different years when the max hires were achieved. Change MIN in the SELECT clause to MAX and you will get the most recent year when the highest number of hires happened. However, often we want a query that, in the case of ties, will return all the years tied for most hires. That cannot be done with the FIRST/LAST function.

    For that, a compact solution would add an analytic function to your original query, to rank the years by number of hires. Then in an outer query just filter for the rows where rank = 1.

    select yr, emp_count
    from   (
             select   extract(year from hiredate) as yr, count(empno) as emp_count,
                      rank() over (order by count(empno) desc) as rnk
             from     emp
             group by extract(year from hiredate)
           )
    where  rnk = 1
    ;
    

    Or, using the max() analytic function in the SELECT clause of the subquery (instead of a rank-type analytic function):

    select yr, emp_count
    from   (
             select   extract(year from hiredate) as yr, count(empno) as emp_count,
                      max(count(empno)) over () as max_count
             from     emp
             group by extract(year from hiredate)
           )
    where  emp_count = max_count
    ;