Problem regarding Exercise 4

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

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)

Hi Tanika!

Thanks for reaching out!

Please accept my apologies for the delayed response.
Thanks for sharing this piece of information with the Community!
You are absolutely right. The COUNT() function counts the existence of a value, whereas  the SUM() function calculate the sum of some values.
 

Hope this helps.
Best,
Martin