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

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?

CodePudding user response：

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