0

I'm trying to add an extra column to my query. Based on the avarage salary, it should write BELOW or ABOVE into the column, but it doesn't work. What is the problem?

SELECT CONCAT(firstname, lastname) AS name
FROM workers
CASE
  WHEN salary < avg(salary) THEN 'BELOW'
  WHEN salary > avg(salary) THEN 'ABOVE'
  ELSE 'AVARAGE'
END;
dmbdnr
  • 334
  • 3
  • 19
  • It would be helpful to say *what* error it gives you. But [you can't use aggregates in a `where` clause](http://stackoverflow.com/q/42470849/266304). – Alex Poole Mar 03 '17 at 12:30

1 Answers1

1

The case belongs in the select clause:

SELECT CONCAT(firstname, lastname) AS name
       (CASE WHEN salary < avg(salary) THEN 'BELOW'
             WHEN salary > avg(salary) THEN 'ABOVE'
             ELSE 'AVERAGE'
        END)
FROM workers;

That fixes the first problem. The next problem is that you have an aggregation function but no aggregation. What you really seem to want is an analytic function:

SELECT CONCAT(firstname, lastname) AS name
       (CASE WHEN salary < AVG(salary) OVER () THEN 'BELOW'
             WHEN salary > AVG(salary) OVER () THEN 'ABOVE'
             ELSE 'AVERAGE'
        END)
FROM workers;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • thank you, this is exactly what I've wanted. Can you explain what do you mean by "you have an aggregation function but no aggregation"? – dmbdnr Mar 03 '17 at 12:33
  • @dmbdnr . . . Your query uses `avg()` -- along with other columns -- but there is no `group by`. – Gordon Linoff Mar 03 '17 at 13:20