SQL Aggregate Functions Tutorial

Example Table

We will use the following table named companies for our examples:

    CREATE TABLE companies (
        id INT PRIMARY KEY,
        name VARCHAR(100),
        address VARCHAR(255),
        zip VARCHAR(10),
        city VARCHAR(100),
        revenue DECIMAL(15, 2)
    );

    INSERT INTO companies (id, name, address, zip, city, revenue) VALUES
    (1, 'Company 1', 'Street A, 123', '12345', 'City A', 100000.00),
    (2, 'Company 2', 'Street B, 456', '98765', 'City B', 200000.00),
    (3, 'Company 3', 'Street C, 789', '56789', 'City C', 150000.00),
    (4, 'Company 4', 'Street D, 234', '67890', 'City D', 250000.00),
    (5, 'Company 5', 'Street E, 567', '78901', 'City E', 300000.00);
    

id	name	address	zip	city	revenue
1	Company 1	Street A, 123	12345	City A	100000.00
2	Company 2	Street B, 456	98765	City B	200000.00
3	Company 3	Street C, 789	56789	City C	150000.00
4	Company 4	Street D, 234	67890	City D	250000.00
5	Company 5	Street E, 567	78901	City E	300000.00

Using COUNT

The COUNT function returns the number of rows that match a specified condition.

SELECT COUNT(*) AS total_companies FROM companies;

Result:

total_companies	5

Using SUM

The SUM function returns the total sum of a numeric column.

SELECT SUM(revenue) AS total_revenue FROM companies;

Result:

total_revenue	1000000.00

Using AVG

The AVG function returns the average value of a numeric column.

SELECT AVG(revenue) AS average_revenue FROM companies;

Result:

average_revenue	200000.00

Using MIN

The MIN function returns the smallest value of the selected column.

SELECT MIN(revenue) AS minimum_revenue FROM companies;

Result:

minimum_revenue	100000.00

Using MAX

The MAX function returns the largest value of the selected column.

SELECT MAX(revenue) AS maximum_revenue FROM companies;

Result:

maximum_revenue	300000.00