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

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.