In SQL, aggregate functions are used to perform calculations on multiple rows of data and return a single value. These are essential for summarizing, analyzing, and reporting data efficiently.
Common Aggregate Functions
COUNT: Returns the number of rows that match a specified condition.
This query counts all employees in the table.
Practice: Write a query to count all clients in the table.
SUM: Returns the total sum of a numeric column.
This query calculates the total salaries.
Practice: Write a query to calculate the total sales.
AVG: Returns the average value of a numeric column.
Finds the average salary.
Practice: Write a query to find the average sales.
MIN: Returns the smallest value in a column.
Practice: Write a query to find the smallest sale.
MAX: Returns the largest value in a column.
Practice: Write a query to find the biggest sale.
GROUP BY - Grouping Data
The GROUP BY clause groups rows that have the same values in specified columns, allowing aggregate functions to be applied to each group.
This query counts employees in each department.
Practice: Write a query to count all the projects assigned to the same employee.
HAVING - Filtering Aggregated Data
While the WHERE clause filters rows before grouping, HAVING filters groups after aggregation.
This query returns only departments with more than 5 employees. Here, no rows satisfy this condition so we do not get any result.
Practice: Write a query to find the all employees having 2 projects assigned to them.
WHERE vs HAVING
| Criteria | WHERE Clause | HAVING Clause |
|---|---|---|
| Purpose | Filters rows before aggregation. | Filters rows after aggregation. |
| Used with | Works with individual rows. | Works with grouped or aggregated data. |
| Applicable to | Columns in the table (individual records). | Aggregated results (e.g., SUM(), COUNT()). |
| Stage of Execution | Filters data before GROUP BY. | Filters data after GROUP BY. |
| Aggregation Functions | Cannot be used with aggregate functions. | Can be used with aggregate functions. |
FAQs
1. Can we use aggregate functions in the WHERE clause?
No. Aggregate functions cannot be used directly in the WHERE clause because WHERE filters rows before aggregation happens. Instead, you should use the HAVING clause to filter aggregated results.
2. What happens if we use non-aggregated columns without GROUP BY in SQL?
Most SQL databases will throw an error if you select a column that is not part of the GROUP BY or wrapped in an aggregate function.
3. Can aggregate functions be nested inside each other?
No, aggregate functions generally cannot be nested (e.g., SUM(AVG(salary)) is invalid). Instead, you can use subqueries to achieve similar results.
4. Do aggregate functions ignore NULL values in SQL?
Yes. Functions like SUM, AVG, MIN, and MAX ignore NULL values. However, COUNT(*) includes NULLs (since it counts rows), while COUNT(column) ignores NULLs.
5. Can aggregate functions be used in subqueries?
Yes. Aggregate functions are commonly used in subqueries to calculate summary values that can be compared in the main query.