3 ms·
SELECT department_id, count(*) AS employees_no FROM employee WHERE gender = 'F' GROUP BY department_id HAVING employees_no < 10; This phrase re
by CPLX 5y ago
SELECT department_id, count(*) AS employees_no
FROM employee
WHERE gender = 'F'
GROUP BY department_id
HAVING employees_no < 10;
This phrase returns departments where the number of female employees is <10 right, rather than listing female employees from departments that have <10 employees in total?
- deleted 5y ago[deleted]
- cm2187 5y agoshould return an error, the select part is read last by the DB, so whatever you define in the select part (employees_no) is not available to the having clause. SELECT department_id, count(*) AS employees_no FROM employee WHERE gender = 'F' GROUP BY department_id HAVING count(*) < 10; The logical order in which the DB processes the query is: FROM Employee WHERE gender = 'F' GROUP BY department_id HAVING count(*) < 10 SELECT department_id, count(*) as employees_no so you apply count(*) < 10 after you filtered by gender and after you grouped by department_id
- magicalhippo 5y ago> should return an error Except some databases allow for that syntax. The one we use, SQLAnywhere, does.
- totoglazer 5y agoDepends on the db. Plenty make such aliases available for having & order by.
- deleted 5y ago[deleted]
- wccrawford 5y agoThat sounds right to me. It creates counts of female employees in each department, and then only outputs the departments that have 10 or more.