CODEWITHSUNDEEP
sqloracle-databaseoracle12c
user
user

Reputation: 4830

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.

Upvotes: 1

Views: 848

Answers (4)

fhossfel
fhossfel

Reputation: 2181

I assume you want a single row showing the year most people where hired:

SELECT * 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)
    ORDER BY COUNT(*))
WHERE ROWNUM=1;

Upvotes: 0

user5683823
user5683823

Reputation:

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
;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269713

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;

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

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  )

Upvotes: 1

Related Questions