Aggregation functions allow you to summarise information from groups of records or an entire table.
- COUNT – Returns the total number of selected rows
- SUM – Adds up all the values in a column
- MIN – Gives the minimum value of a column
- MAX – Gives the maximum value of a column
- AVG – Calculates the average value of a column
COUNT() — Count rows
Returns the total number of rows that meet a condition.
-- Total number of clients
SELECT COUNT(*) AS total_clients
FROM Clients;
-- Number of clients in Barcelona
SELECT COUNT(*) AS clients_barcelona
FROM Clients
WHERE City = 'Barcelona';
COUNT(*) counts all rows, while COUNT(column) only counts rows where the column value is not NULL.
SUM() — Add values
Adds up all numeric values in a column.
-- Total sales
SELECT SUM(amount) AS total_sales
FROM Sales;
-- Total sales made by a specific salesperson
SELECT SUM(amount) AS total_sales_marc
FROM Sales
WHERE salesperson = 'Marc Solé';
MIN() — Minimum value
Shows the lowest value in a column.
-- Minimum employee salary
SELECT MIN(salary) AS min_salary
FROM Employees;
-- Youngest client age
SELECT MIN(Age) AS youngest_age
FROM Clients;
MAX() — Maximum value
Shows the highest value in a column.
-- Highest salary
SELECT MAX(salary) AS max_salary
FROM Employees;
-- Oldest client age
SELECT MAX(Age) AS oldest_age
FROM Clients;
AVG() — Average value
Calculates the average value of a numeric column.
-- Average employee salary
SELECT AVG(salary) AS avg_salary
FROM Employees;
-- Average client age
SELECT AVG(Age) AS avg_age
FROM Clients;
Combined example with GROUP BY
-- Average salary per department
SELECT Department, AVG(salary) AS avg_salary
FROM Employees
GROUP BY Department;
