Aggregate functions (SUM, COUNT, etc.) — where can they be used in an SQL query? What is the difference between WHERE and HAVING when using aggregate functions?

Aggregate functions (SUM, COUNT, AVG, MAX, MIN, etc.) are used in SQL to compute summary values for groups of data. They can be applied in:

• SELECT — to obtain aggregated values
• HAVING — to filter groups based on aggregates

The difference between WHERE and HAVING:

• WHERE filters rows before grouping, i.e., it works with the raw data.
• HAVING filters grouped data, i.e., it works with the results of aggregate functions.

Example:

SELECT department, COUNT(*) as employee_count
FROM employees
WHERE salary > 50000  -- filter employees with salary > 50k
GROUP BY department
HAVING COUNT(*) > 10    -- select departments with more than 10 employees

Here, WHERE first filters rows, then grouping occurs, and HAVING filters groups based on the count.