22 May 2024

Posted on:

13 Jul 2023

0

Problem regarding Exercise 4

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?

Instructor
Posted on:

21 Jul 2023

0

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

Posted on:

19 Sept 2023

0

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)

Instructor
Posted on:

22 May 2024

0

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