Problem regarding Exercise 4

Question

SELECT COUNT(CASE WHEN s.salary < c.avg_salary THEN 1 ELSE 0 END) as no_of_male_salaries_below_avg
FROM salaries s
CROSS JOIN (SELECT AVG(salary) as avg_salary FROM salaries) c
WHERE s.emp_no IN (SELECT emp_no FROM employees WHERE gender = 'M');

Why above query is giving an incorrect output regarding exercise 4. Can you pls explain?

Answer 1

Hi Vaibhav!
Thanks for reaching out.

It is because you used the COUNT() function instead of the SUM() function. Please, use the following code:

SELECT SUM(CASE WHEN s.salary < c.avg_salary THEN 1 ELSE 0 END) as no_of_male_salaries_below_avg
FROM salaries s
CROSS JOIN (SELECT AVG(salary) as avg_salary FROM salaries) c
WHERE s.emp_no IN (SELECT emp_no FROM employees WHERE gender = 'M');

Hope this helps.
Best,
Tsvetelin

Answer 2

COUNT function counts the "existence" of a value. If you pass 0,1, 2 or any other integer/string/value to COUNT function it will be counted. However, if you pass in NULL as value to COUNT function, then it will not count it as it does not exist.

The problem with your query above is you are counting values no matter if the condition in CASE statement is satisfied or not.

If you want to use COUNT function in your query, you need to use the following expression :

COUNT(CASE WHEN s.salary < c.avg_sal THEN 1 ELSE NULL END)

Problem regarding Exercise 4

Submit an answer

related questions