MySQL Aggregate Functions

Aggregate functions in MySQL perform a calculation on a set of rows and return a single result. Instead of showing individual row values, they summarize the data — such as finding the total, average, count, or highest value in a column.

Common Aggregate Functions

Sample Table Used in Examples

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    salesperson VARCHAR(100),
    region VARCHAR(50),
    amount DECIMAL(10,2)
);

INSERT INTO sales VALUES (1, 'Arjun', 'North', 15000);
INSERT INTO sales VALUES (2, 'Priya', 'South', 22000);
INSERT INTO sales VALUES (3, 'Arjun', 'North', 18000);
INSERT INTO sales VALUES (4, 'Meena', 'East', 9500);
INSERT INTO sales VALUES (5, 'Priya', 'South', 31000);
INSERT INTO sales VALUES (6, 'Meena', 'East', 12000);

COUNT()

COUNT() returns the number of rows that match the query.

-- Count total rows
SELECT COUNT(*) AS total_sales FROM sales;

-- Count non-NULL values in a specific column
SELECT COUNT(salesperson) AS total_salespersons FROM sales;

total_sales
-----------
6

SUM()

SUM() adds up all values in a numeric column.

SELECT SUM(amount) AS total_revenue FROM sales;

total_revenue
-------------
107500.00

AVG()

AVG() calculates the average value of a numeric column.

SELECT AVG(amount) AS average_sale FROM sales;

average_sale
------------
17916.666667

MAX()

MAX() returns the largest value in a column.

SELECT MAX(amount) AS highest_sale FROM sales;

highest_sale
------------
31000.00

MIN()

MIN() returns the smallest value in a column.

SELECT MIN(amount) AS lowest_sale FROM sales;

lowest_sale
-----------
9500.00

Using Multiple Aggregate Functions Together

SELECT
    COUNT(*) AS total_transactions,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_sale,
    MAX(amount) AS highest,
    MIN(amount) AS lowest
FROM sales;

Aggregate Functions with WHERE

Aggregate functions can be filtered using WHERE to summarize specific subsets of data.

SELECT SUM(amount) AS south_revenue
FROM sales
WHERE region = 'South';

south_revenue
-------------
53000.00

COUNT(*) vs COUNT(column)

Key Points

• Aggregate functions compute a single summary value from multiple rows.
• COUNT(*) counts all rows; COUNT(column) skips NULL values.
• SUM() and AVG() work only on numeric columns.
• MAX() and MIN() work on numbers, strings, and dates.
• Aggregate functions are typically combined with GROUP BY for grouped summaries.