ORA-00979: not a GROUP BY expression

Question

What is wrong with my group by? SQL Fiddle

with age_range as (
    select 0 as "bottom", 29 as "top", '<30' as "range" from dual union
    select 30, 34, '30-34' from dual union
    select 35, 39, '35-59' from dual union
    select 40, 49, '40-49' from dual union
    select 50, 54, '50-54' from dual
)
select 
    ar."range" as Age,
    count(case when Department = 'IT' and Gender = 'M' then 1 end) as IT_Male,
    count(case when Department = 'IT' and Gender = 'F' then 1 end) as IT_Female,
    count(case when Department = 'Finance' and Gender = 'M' then 1 end) as Finance_Male,
    count(case when Department = 'Finance' and Gender = 'F' then 1 end) as Finance_Female,
    count(case when Department = 'HR' and Gender = 'M' then 1 end) as HR_Male,
    count(case when Department = 'HR' and Gender = 'F' then 1 end) as HR_Female
from 
    JOB_Details jd
    inner join
    age_range ar on jd.Age between ar."bottom" and ar."top"
group by ar."range"
order by ar."bottom"

score 2 · Accepted Answer · answered Oct 12 '12 at 10:59

If you want to ORDER BY ar.bottom then you need to include it in the GROUP BY:

with age_range as 
(
    select 0 as bottom, 29 as top, '<30' as range from dual union
    select 30, 34, '30-34' from dual union
    select 35, 39, '35-59' from dual union
    select 40, 49, '40-49' from dual union
    select 50, 54, '50-54' from dual
)
select 
    ar.range as Age,
    count(case when Department = 'IT' and Gender = 'M' then 1 end) as IT_Male,
    count(case when Department = 'IT' and Gender = 'F' then 1 end) as IT_Female,
    count(case when Department = 'Finance' and Gender = 'M' then 1 end) as Finance_Male,
    count(case when Department = 'Finance' and Gender = 'F' then 1 end) as Finance_Female,
    count(case when Department = 'HR' and Gender = 'M' then 1 end) as HR_Male,
    count(case when Department = 'HR' and Gender = 'F' then 1 end) as HR_Female
from JOB_Details jd
inner join age_range ar 
  on jd.Age between ar.bottom and ar.top
group by ar.range, ar.bottom
order by ar.bottom

See SQL Fiddle with Demo

score 1 · Answer 2 · answered Oct 12 '12 at 10:57

1

Remove your last:

order by ar."bottom"

answered Oct 12 '12 at 10:57

ferpega

3,182
7
45
65

ORA-00979: not a GROUP BY expression

2 Answers2