CODEWITHSUNDEEP
sqloracle-database
user3729062
user3729062

Reputation: 43

CASE in Aggregate Functions

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?

Upvotes: 1

Views: 128

Answers (2)

StuartLC
StuartLC

Reputation: 107247

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269643

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.

Upvotes: 6

Related Questions