CODEWITHSUNDEEP
sql
Chowis
Chowis

Reputation: 33

How can I retrieve CASE output in the same column with AVG function output?

I need to retrieve department name and average salary in each department except case when there are no employees in department, in that case the output should be 0 in the same column as average salary. Tables.

Firstly I wrote the code :

SELECT Department.NAME, AVG(Employee.salary) as avg_slr,
CASE 
WHEN Employee.ID IS NULL
THEN 0 
END 
FROM Department
LEFT JOIN Employee ON Department.ID = Employee.department_ID
GROUP BY Department.NAME, Employee.ID

As expected, CASE construction creates another column but how can I avoid it and keep only two columns in output?

Upvotes: 1

Views: 40

Answers (1)

LukStorms
LukStorms

Reputation: 29667

Just default the NULL averages to 0

SELECT Department.NAME
, COALESCE(AVG(Employee.salary), 0) as avg_slr
FROM Department
LEFT JOIN Employee ON Department.ID = Employee.department_ID
GROUP BY Department.NAME

Test on db<>fiddle here

Note that it is possible to wrap the AVG in a CASE WHEN.
But a CASE WHEN would be more useful for complexer logic.

SELECT Department.NAME
, CASE
  WHEN AVG(Employee.salary) IS NOT NULL
  THEN AVG(Employee.salary) 
  ELSE 0
  END as avg_slr
FROM Department
LEFT JOIN Employee ON Department.ID = Employee.department_ID
GROUP BY Department.NAME

Upvotes: 3

Related Questions