CODEWITHSUNDEEP
sqloracle-databasegroup-by
Hadh
Hadh

Reputation: 337

SQL order by with group by query

I have an EMPLOYEES table : 

employee_id(1) hire_date(15-2-2001)
employee_id(2) hire_date(2-2-1999)
employee_id(3) hire_date(11-2-2003)
employee_id(4) hire_date(6-7-2001)

I want to display the YEAR with the highest number of employees hired in, with the number of employees hired each month. I tried this : 

select extract (year from hire_date) 
from employees 
where max(count(employee_id))=count(employee_id) 
order by extract (year from hire_date);

and I keep getting an "ORA-00934: group function is not allowed here"

What am I doing wrong?

I'm using ORACLE 10g Express.

Upvotes: 0

Views: 68

Answers (2)

Hadh
Hadh

Reputation: 337

With PL/SQL I found this :

declare    
    recuperation float;
    CURSOR newRequest(recuperationDonnes float) is select count(employee_id) as nombreEmployes,
      extract(month from hire_date) as mois from employees where
      extract(year from hire_date) = (recuperationDonnes) group by extract(month from hire_date);
    a float;
    a1 float;
begin
    select extract (year from hire_date) as annee into recuperation from employees having count
     (employee_id)  >= all (select  count (employee_id) as emp from employees group by extract(year 
     from hire_date)) group by extract(year from hire_date);
     OPEN newRequest(recuperation);
     LOOP
        FETCH newRequest into a,a1;
        Exit when newRequest%NotFound;
        dbms_output.put_Line('Year: '||recuperation||' mois: '||a1||' nombreEmployes: '||a);
     END LOOP ;
     CLOSE newRequest;
end;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

The idea is that you can use aggregation and window functions to get the totals by month and year. Then you can choose the largest using row_number() or dense_rank().

select ym.*
from (select ym.*, dense_rank() over (order by year_cnt, year) as seqnum
      from (select extract(year from hire_date) as yyyy,
                   extract(month from hire_date) as mm,
                   count(*) as cnt,
                   sum(count(*)) over (partition by extract(year from hire_date)) as year_cnt
            from employees 
            group by extract(year from hire_date),
                     extract(month from hire_date)
           ) ym
     ) ym
where seqnum = 1
order by yyyy, mm;

Hmmmm, you can do this without so many subqueries:

with ym as (
      select extract(year from hire_date) as yyyy   
             extract(month from hire_date) as mm,
             count(*) as cnt,
             sum(count(*)) over (partition by extract(year from hire_date)) as yearcnt
      from employees 
      group by extract(year from hire_date), extract(month from hire_date)
     )
select *
from ym
where yearcnt = (select max(yearcnt) from ym)
order by yyyy, mm;

Of course, this returns multiple years if two years have the same maximum value.

Upvotes: 2

Related Questions