MySQL Aggregate Functions

Aggregate functions in MySQL allow us to perform calculations on a set of values to return a single result. These functions are especially useful in data analysis, as they allow us to calculate sums, averages, count records, and find extreme values. They are commonly used in combination with the GROUP BY clause.

EXAMPLE

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10,2)
);

INSERT INTO employees (id, first_name, department, salary) VALUES (1, 'Janez', 'Production', 1800.00);
INSERT INTO employees (id, first_name, department, salary) VALUES (2, 'Marija', 'Production', 2000.00);
INSERT INTO employees (id, first_name, department, salary) VALUES (3, 'Anton', 'Sales', 2500.00);
INSERT INTO employees (id, first_name, department, salary) VALUES (4, 'Ana', 'Sales', 1900.00);
INSERT INTO employees (id, first_name, department, salary) VALUES (5, 'Boris', 'Production', 1950.00);

SELECT COUNT(*) AS employee_count FROM employees;

SELECT department, COUNT(*) AS employee_count 
FROM employees 
GROUP BY department;

SELECT department, AVG(salary) AS average_salary 
FROM employees 
GROUP BY department;

SELECT department, COUNT(*) AS employee_count, MIN(salary) AS lowest_salary, MAX(salary) AS highest_salary, AVG(salary) AS average_salary
FROM employees 
GROUP BY department;

SELECT * FROM employees WHERE salary > 1900;

SELECT COUNT(*) AS total
FROM employees
WHERE department = 'Production';

SELECT DISTINCT department FROM employees;

SELECT COUNT(DISTINCT department) AS department_count FROM employees;

RESULT

Number of Employees

Counts the total number of all employees in the table.

SELECT COUNT(*) AS employee_count FROM employees;

Number of Employees by Department

Displays the number of employees in each department.

SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department;

Average Salary by Department

Calculates the average salary for each individual department.

SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department;

Average Salary by Department and Number of Employees

Displays the number of employees, lowest salary, highest salary, and average salary for each department.

SELECT department, COUNT(*) AS employee_count, MIN(salary) AS lowest_salary, MAX(salary) AS highest_salary, AVG(salary) AS average_salary FROM employees GROUP BY department;

Data of Employees with a Higher Salary

Displays all data about employees who have a salary higher than €1900.

SELECT * FROM employees WHERE salary > 1900;

Counting Employees

Counts the number of employees in the Production department.

SELECT COUNT(*) AS total FROM employees WHERE department = 'Production';

Display of Departments in the Company

Displays a list of all distinct departments in the company, without repetition.

SELECT DISTINCT department FROM employees;

Counting Departments

Counts the number of distinct departments in the company.

SELECT COUNT(DISTINCT department) AS department_count FROM employees;

MySQL Aggregate Functions Quick Cheat Sheet

Aggregate functions in MySQL process multiple values from various rows into a single summary result. They are heavily utilized for statistics, generating reports, and data analysis, usually in combination with the GROUP BY clause.

Name / Function	Description and Behavior	Syntax (HTML)	Simulator Example
COUNT()	Counts the total number of rows within each group (ignores NULL values).	`COUNT(column)`	`SELECT department, COUNT(*) FROM employees GROUP BY department;`
SUM()	Calculates and returns the collective sum of numeric values for each specific group.	`SUM(column)`	`SELECT department, SUM(salary) FROM employees GROUP BY department;`
AVG()	Calculates the mathematical average value of the numbers within each group.	`AVG(column)`	`SELECT department, AVG(salary) FROM employees GROUP BY department;`
MIN()	Finds and returns the lowest (minimum) value found within each separate group.	`MIN(column)`	`SELECT department, MIN(salary) FROM employees GROUP BY department;`
MAX()	Finds and returns the highest (maximum) value found within each separate group.	`MAX(column)`	`SELECT department, MAX(salary) FROM employees GROUP BY department;`
GROUP_CONCAT()	Combines text values from multiple rows into a single, comma-separated text string per group.	`GROUP_CONCAT(column)`	`SELECT department, GROUP_CONCAT(first_name) FROM employees GROUP BY department;`