Oracle SQL WHEN condition in SELECT statement-CodePudding

I have the following select statement (example) which gives back a sum from a table.

Select SUM(Salary) from Users d, MoreData m where
d.Office = 'A' and
m.Active = 'Yes' and
d.id_user = m.id_of_user

This works perfectly fine and adds up column salery for all users which are located in Office A abd are marked as active in the table MoreData.

Now I would only like to get a result if the SUM(Salary) under these conditions is greater than 1.000.000 EUR. If not the result would be just NULL.

Thanks in Advance

I tried adding an CASE WHEN statement but this always gives ORA-00900 back.

It needs to be an SELECT statement.

CodePudding user response：

That looks like a having clause:

Select SUM(Salary) from Users d, MoreData m where
d.Office = 'A' and
m.Active = 'Yes' and
d.id_user = m.id_of_user
having sum(salary) > 1000000       --> this

Though, I presume that you'd actually want to get some more info, such as which user has such a salary, so you'd have to add that column into the select column list and then group by it. Also, to follow good practice, switch to join:

  SELECT d.id_user, 
         SUM (salary)
    FROM users d JOIN moredata m ON m.id_of_user = d.id_user
   WHERE     d.office = 'A'
         AND m.active = 'Yes'
GROUP BY d.id_user
  HAVING SUM (salary) > 1000000