SQL Aggregate Functions

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);
    

Using COUNT

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

SELECT COUNT(*) AS total_companies FROM companies;

Result:

Using SUM

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

SELECT SUM(revenue) AS total_revenue FROM companies;

Result:

Using AVG

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

SELECT AVG(revenue) AS average_revenue FROM companies;

Result:

Using MIN

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

SELECT MIN(revenue) AS minimum_revenue FROM companies;

Result:

Using MAX

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

SELECT MAX(revenue) AS maximum_revenue FROM companies;

Result: