SQL Aggregate Functions & Grouping: COUNT, SUM, GROUP BY, HAVING

What Are Aggregate Functions?

Aggregate functions are special SQL functions that operate on multiple rows and return a single summarized value. They are commonly used with the GROUP BY clause to perform calculations for each group of rows.

The most commonly used aggregate functions are:

• COUNT() – Counts the number of rows or non-NULL values.
• SUM() – Adds all values in a numeric column.
• AVG() – Calculates the average of numeric values.
• MIN() – Returns the smallest value.
• MAX() – Returns the largest value.

Common Aggregate Functions

Assume an orders table with the following columns:

• order_id
• customer_id
• amount

Calculate the Total Sales

SELECT SUM(amount)
FROM orders;

Count the Total Number of Orders

SELECT COUNT(*)
FROM orders;

Calculate the Average Order Amount

SELECT AVG(amount)
FROM orders;

Find the Minimum Order Amount

SELECT MIN(amount)
FROM orders;

Find the Maximum Order Amount

SELECT MAX(amount)
FROM orders;

Practical Use Case

Aggregate functions are widely used for reporting and analytics.

For example, to summarize sales data, you can use:

• SUM(amount) – Total sales
• COUNT(*) – Total number of orders
• AVG(amount) – Average order value

These summaries help in business reporting and decision-making.

Aggregate Functions Without GROUP BY

Aggregate functions work even without the GROUP BY clause.

When GROUP BY is omitted, SQL treats the entire table as a single group and returns one summarized result.

Example

SELECT AVG(amount)
FROM orders;

This query returns the average amount across all orders.

How Aggregate Functions Handle NULL Values

Most aggregate functions ignore NULL values.

The only exception is:

• COUNT(*) counts every row, including rows containing NULL values.

Therefore:

• SUM(), AVG(), MIN(), and MAX() ignore NULL values.
• AVG() automatically calculates the average using only non-NULL values.

COUNT(*) vs COUNT(column)

Although both functions count records, they behave differently.

COUNT(*)

• Counts every row in the table.
• Includes rows containing NULL values.

COUNT(column)

• Counts only rows where the specified column is not NULL.

Example

SELECT COUNT(*)
FROM employees;

SELECT COUNT(email)
FROM employees;

If some employees have a NULL value in the email column:

• COUNT(*) returns the total number of employees.
• COUNT(email) returns only employees with an email address.

What Is GROUP BY?

The GROUP BY clause groups rows that contain the same values in one or more columns.

It is commonly used with aggregate functions to calculate summary values for each group.

Syntax

SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name;

Example

SELECT city, COUNT(*) AS student_count
FROM students
GROUP BY city;

Output

The query returns the number of students in each city.

Rule of Thumb for GROUP BY

Whenever you use GROUP BY:

• Every column in the SELECT clause should either:
  • Be included in the GROUP BY clause, or
  • Be used inside an aggregate function.

You can also group by multiple columns.

Example

SELECT department_id, job_title, AVG(salary)
FROM employees
GROUP BY department_id, job_title;

This summarizes salaries by both department and job title.

GROUP BY vs DISTINCT

Although they may appear similar, they serve different purposes.

Use DISTINCT When

You simply want to remove duplicate values.

SELECT DISTINCT city
FROM students;

Use GROUP BY When

You want to calculate summary values.

SELECT city, COUNT(*)
FROM students
GROUP BY city;

GROUP BY vs ORDER BY

GROUP BY and ORDER BY have different purposes.

You can use both together.

Example

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
ORDER BY employee_count DESC;

This query:

1. Groups employees by department.
2. Counts employees in each department.
3. Sorts the departments from highest to lowest employee count.

The HAVING Clause

The HAVING clause filters grouped data after the GROUP BY operation.

Unlike WHERE, which filters rows before grouping, HAVING filters groups after aggregation.

Syntax

SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;

Example

Find products whose total quantity sold is greater than 10.

SELECT product, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product
HAVING SUM(quantity) > 10;

How It Works

1. SQL groups all rows by product.
2. Calculates the total quantity for each product.
3. Keeps only products whose total quantity is greater than 10.

WHERE vs HAVING

WHERE

• Filters individual rows.
• Executes before grouping.
• Cannot use aggregate functions in its conditions.

HAVING

• Filters grouped data.
• Executes after grouping.
• Can use aggregate functions such as SUM(), COUNT(), and AVG().

Quick Comparison

Try It: One Table, Real Results

We ran these queries on MariaDB against a small orders table — seven orders, two of them with a NULL amount (a canceled and a pending order):

CREATE TABLE orders (order_id INT PRIMARY KEY, customer VARCHAR(20), city VARCHAR(20),
                     status VARCHAR(12), amount DECIMAL(8,2));
INSERT INTO orders VALUES
 (1,'Asha','Pune','PAID',500.00), (2,'Asha','Pune','PAID',300.00), (3,'Ravi','Delhi','PAID',1200.00),
 (4,'Ravi','Delhi','CANCELLED',NULL), (5,'Meera','Pune','PAID',250.00), (6,'Kiran','Chennai','PENDING',NULL),
 (7,'Asha','Pune','PAID',300.00);

1. NULLs change your numbers

SELECT COUNT(*) AS all_rows, COUNT(amount) AS rows_with_amount, SUM(amount) AS total,
       AVG(amount) AS avg_ignoring_nulls, AVG(COALESCE(amount, 0)) AS avg_nulls_as_zero
FROM orders;

Two "averages" of the same data differ by almost 150, depending on whether NULL means "unknown" (skip it) or "zero". When you validate a report's average against the database, find out which one the business rule means.

2. WHERE, GROUP BY and HAVING together

SELECT city, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
WHERE status = 'PAID'           -- filter rows first
GROUP BY city
HAVING SUM(amount) > 400        -- then filter groups
ORDER BY revenue DESC;

Chennai disappears at the WHERE step (its only order is pending); every remaining city passes the HAVING threshold. The order of evaluation is: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

3. Find duplicate records — a tester's favorite

SELECT customer, amount, COUNT(*) AS times
FROM orders
GROUP BY customer, amount
HAVING COUNT(*) > 1;

The same pattern finds duplicate emails, double-submitted payments or repeated order lines after a retry — some of the most valuable bugs testers find with SQL.

4. Two errors you'll meet

• WHERE COUNT(*) > 1 → ERROR 1111: Invalid use of group function. Aggregates can't go in WHERE; use HAVING.
• SELECT city, customer, COUNT(*) … GROUP BY city with ONLY_FULL_GROUP_BY on (the default in MySQL 5.7+) → ERROR 1055: 'customer' isn't in GROUP BY. Every selected column must be grouped or aggregated. Some databases with that mode off return an arbitrary value instead — which is worse, because it looks correct.

Practice these under interview conditions in the SQL Interview Simulator. More: Top 25 SQL Questions for Testers and the SQL Cheat Sheet.

📚 Official documentation: MySQL Reference Manual

FAQs

1. What Are Aggregate Functions in SQL?

Aggregate functions operate on multiple rows and return a single summarized value.

The most commonly used aggregate functions are:

• COUNT()
• SUM()
• AVG()
• MIN()
• MAX()

2. Do Aggregate Functions Work Without GROUP BY?

Yes. Without a GROUP BY clause, aggregate functions treat the entire table as one group and return a single summarized result.

3. How Do Aggregate Functions Handle NULL Values?

Aggregate functions ignore NULL values, except COUNT(*), which counts every row regardless of NULL values.

4. What Is the Difference Between COUNT(*) and COUNT(column)?

COUNT(*)

• Counts every row.
• Includes rows with NULL values.

COUNT(column)

• Counts only rows where the specified column is not NULL.

5. What Is GROUP BY?

The GROUP BY clause groups rows that have the same values in one or more columns. It is commonly used with aggregate functions to calculate summaries for each group.

6. How Is GROUP BY Different from DISTINCT?

DISTINCT

• Removes duplicate rows.
• Does not perform aggregation.

GROUP BY

• Groups similar rows.
• Enables aggregate functions to calculate summaries for each group.

7. What Is the Difference Between WHERE and HAVING?

WHERE

• Filters individual rows before grouping.
• Cannot use aggregate functions.

HAVING

• Filters groups after the GROUP BY operation.
• Can use aggregate functions in its conditions.