Grouping and Aggregating

Question 1

return the frequency of each make of car, where you have column ‘make’ in table ‘cars’

Answer 1

SELECT make, COUNT(*)
FROM cars
GROUPBY make

Question 2

What’s wrong with the following:

SELECT department, COUNT(employee_id)
FROM employees
GROUP BY department
WHERE 1=1

Answer 2

WHERE needs to go before GROUP BY

Question 3

What’s wrong with the following:

SELECT department, COUNT(*)
FROM employees
WHERE COUNT(*) < 35
GROUP BY department
ORDER BY department

Answer 3

This would fail, because WHERE clauses are for evaluating RECORDS, not AGGREGATIONS. If you want to evaluate aggregations, you need to use the keyword HAVING instead and put that clause AFTER the GROUP BY

Question 4

What’s the difference between WHERE and HAVING?

Answer 4

WHERE evaluates records, while HAVING evaluates aggregations. WHERE clauses will go before GROUP BY statements, while HAVING clauses will go after