SQL Aggregate Functions
In this lesson, you will learn how to use the SQL built-in functions to perform calculations on table data.
The Aggregate Functions In SQL
The SQL aggregate functions can be used to perform calculations on a set of values and return the result as a single value.
The following table contains the most used and useful aggregate functions that can be found in all kinds of databases.
| Function | Description |
|---|---|
MIN() |
Returns the minimum value of the selected column. |
MAX() |
Returns the maximum value of the selected column. |
AVG() |
Returns the average value of the selected column. |
SUM() |
Returns the sum of values of the selected column. |
COUNT() |
Returns the number of rows in a result set. |
MIN(), MAX(), AVG() and SUM() functions ignore NULL values.
Preparing A Database
Since you will learn how to use the aggregation functions, you must have a database table with some records to be able to try them. Execute the following query to create the database.
Example
-- If there is already a database named "demo" delete it
DROP DATABASE IF EXISTS demo;
-- Create a new database called "demo"
CREATE DATABASE demo;
-- Select the "demo" database
USE demo;
-- Create a new table called "employees" inside the selected database
CREATE TABLE employees (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary DECIMAL(7,2),
birthday Date
);
-- Insert 10 records in the "employees" table
-- We add null in the id column because its value will be generated automatically by the database server
INSERT INTO employees VALUES (null, "Emily", "Johnson", 800, "1990-10-23");
INSERT INTO employees VALUES (null, "Benjamin", "Anderson", 750, "1992-05-08");
INSERT INTO employees VALUES (null, "Olivia", "Smith", 900, "1988-07-12");
INSERT INTO employees VALUES (null, "William", "Davis", 845, "1994-12-04");
INSERT INTO employees VALUES (null, "Sophia", "Brown", 1000, "1991-10-27");
INSERT INTO employees VALUES (null, "Daniel", "Martinez", 700, "1993-11-12");
INSERT INTO employees VALUES (null, "Ava", "Taylor", 850, "1995-03-10");
INSERT INTO employees VALUES (null, "James", "Wilson", 920, "1990-02-14");
INSERT INTO employees VALUES (null, "Mia", "Garcia", 780, "1991-08-12");
INSERT INTO employees VALUES (null, "Ethan", "Jones", 880, "1985-09-20");
This is the employees table that we created in the demo database.
| id | first_name | last_name | salary | birthday |
|---|---|---|---|---|
| 1 | Emily | Johnson | 800.00 | 1990-10-23 |
| 2 | Benjamin | Anderson | 750.00 | 1992-05-08 |
| 3 | Olivia | Smith | 900.00 | 1988-07-12 |
| 4 | William | Davis | 845.00 | 1994-12-04 |
| 5 | Sophia | Brown | 1000.00 | 1991-10-27 |
| 6 | Daniel | Martinez | 700.00 | 1993-11-12 |
| 7 | Ava | Taylor | 850.00 | 1995-03-10 |
| 8 | James | Wilson | 920.00 | 1990-02-14 |
| 9 | Mia | Garcia | 780.00 | 1991-08-12 |
| 10 | Ethan | Jones | 880.00 | 1985-09-20 |
SQL MIN Function
The MIN() function is used to return the minimum value of the selected column.
It can be used with numeric and date data types.
The following query returns the minimum salary in the employees table.
Example 1
SELECT MIN(salary) AS 'Minimum Salary'
FROM employees;
Output
| Minimum Salary |
|---|
| 700.00 |
The following query returns the minimum birthday in the employees table.
Example 2
SELECT MIN(birthday) AS 'Minimum birthday'
FROM employees;
Output
| Minimum birthday |
|---|
| 1985-09-20 |
SQL MAX Function
The MAX() function is used to return the maximum value of the selected column.
It can be used with numeric and date data types.
The following query returns the maximum salary in the employees table.
Example 1
SELECT MAX(salary) AS 'Maximum Salary'
FROM employees;
Output
| Maximum Salary |
|---|
| 1000.00 |
The following query returns the maximum birthday in the employees table.
Example 2
SELECT MAX(birthday) AS 'Maximum birthday'
FROM employees;
Output
| Maximum birthday |
|---|
| 1995-03-10 |
SQL AVG Function
The AVG() function is used to return the average value of the selected column.
It can be used with numeric data types only.
The following query returns the average salary in the employees table.
Example
SELECT AVG(salary) AS 'Average Salary'
FROM employees;
Output
| Average Salary |
|---|
| 842.500000 |
SQL SUM Function
The SUM() function is used to return the sum of values of the selected column.
It can be used with numeric data types only.
The following query returns the sum of all salary values in the employees table.
Example
SELECT SUM(salary) AS 'Total Salaries'
FROM employees;
Output
| Total Salaries |
|---|
| 8425.00 |
SQL COUNT Function
The COUNT() function is used to return the number of rows in a result set.
The following query returns the number of all rows in the employees table.
Example
SELECT COUNT(*) AS 'Total Rows'
FROM employees;
Output
| Total Rows |
|---|
| 10 |
The asterisk symbol * returns the number of rows whatever their values.
If you specify a column name instead of using the asterisk, then the number of non-null values under that column will be counted only.
Aggregate Functions Exercise
1- Write a query that creates a new database called "training".
Note: you should recreate it if it already exists.
2- Write a query that creates a table called "users" and check whether it exists or not. the table should contain the following fields:
id INT NOT NULL PRIMARY KEY AUTO_INCREMENTusername VARCHAR(50)revenue DECIMAL(7,2)
3- Write a query that inserts 5 rows in the "users" table.
4- Write a query that returns the total number of users, total revenue, average revenue, minimum and maximum revenue.
Solution
DROP DATABASE IF EXISTS training;
CREATE DATABASE training;
USE training;
CREATE TABLE IF NOT EXISTS users (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50),
revenue DECIMAL(7,2)
);
INSERT INTO users (username, revenue) VALUES
('Jack', 250.25),
('Emma', 410.95),
('Luke', 180.76),
('Jim', 320.00),
('Lily', 360.35);
SELECT
COUNT(*) AS 'Total Users',
SUM(revenue) AS 'Total Revenues',
AVG(revenue) AS 'Average Revenue',
MIN(revenue) AS 'Minimum Revenue',
MAX(revenue) AS 'Maximum Revenue'
FROM users;
