CASE in Aggregate Functions

Question

I looked up this and found some links on the forums here but none that i felt applied to my question, or seemed to have the same result as mine. What I need to do is this...

List all employee last name, first name, salary, and a “compare column” from more_employees tables if salary is more than salary average, this column ouput ‘High-Pay’ else output ‘Low-Pay’

What I've worked out is this...

SELECT first_name, last_name, salary,
       CASE WHEN salary > AVG(salary) THEN 'High-Pay' 
            WHEN salary < AVG(salary) THEN 'Low-Pay'
            ELSE 'Average' AS CompareColumn
       END 
FROM more_employees

The error I'm getting is that this is

"not a single-group group function"

so I add in a group by at the end with anyone one of the column names then I get

"Not a group-by function"

and so I am stuck and have been for awhile now. Any ideas?

Have you read http://stackoverflow.com/questions/1520608/ora-00979-not-a-group-by-expression? — Ben, Oct 21 '14 at 12:01
take the first_name, last_name, salary cols in the group by and will work fine, but fix the "case when" first — Thomas, Oct 21 '14 at 12:14
always when u get the "not group by expression" error means you missed a column from the group by clause — Thomas, Oct 21 '14 at 12:19

score 6 · Accepted Answer · answered Oct 21 '14 at 12:06

You need to calculate the average for the comparison. The easiest way in Oracle is to use analytic functions:

SELECT first_name, last_name, salary,
       (CASE WHEN salary > AVG(salary) OVER () THEN 'High-Pay' 
             WHEN salary < AVG(salary) OVER () THEN 'Low-Pay'
             ELSE 'Average' 
        END) AS CompareColumn
FROM more_employees ;

The as also goes after the end for the case.

StuartLC · Answer 2 · 2014-10-21T12:22:08.770

Project the average salary out (e.g. using a CTE) and then use this in the classification query:

WITH cteSalary AS
(
  SELECT AVG(salary) as avgSalary
  FROM more_employees
)
SELECT 
  e.first_name, 
  e.last_name, 
  e.salary,
  CASE 
    WHEN e.salary > s.avgSalary THEN 'High-Pay'
    WHEN e.salary < s.avgSalary THEN 'Low-Pay'
    ELSE 'Average'
  END AS CompareColumn
FROM more_employees e CROSS JOIN cteSalary s;

SqlFiddle here

CASE in Aggregate Functions

2 Answers2