Class 30 of 58 • Aggregation & Grouping

SQL Aggregate Functions: COUNT, SUM, AVG, MIN & MAX

In corporate business intelligence, decision-makers rarely inspect raw individual transaction records. Instead, they demand aggregate metrics: total annual revenue, average customer spending, minimum delivery times, and total student counts. SQL Aggregate Functions perform a mathematical calculation across multiple row values and return a single summary value.

The 5 Core SQL Aggregate Functions

Function & Syntax	Calculation Performed	NULL Handling
*COUNT()**	Returns the total number of rows matching the query.	Includes rows with NULLs.
COUNT(column)	Returns total count of non-NULL values in that column.	Ignores NULLs.
SUM(numeric_col)	Calculates total sum of all values in the column.	Ignores NULLs.
AVG(numeric_col)	Calculates arithmetic mean ((\sum / N)) of non-NULL values.	Ignores NULLs.
MIN(column)	Finds minimum value (works on numbers, dates, strings).	Ignores NULLs.
MAX(column)	Finds maximum value (works on numbers, dates, strings).	Ignores NULLs.

Hands-on Query: University Statistical Summary

-- Calculate high-level student performance metrics
SELECT 
    COUNT(*) AS total_students_enrolled,
    COUNT(phone_number) AS students_with_phone,
    COUNT(DISTINCT course) AS unique_courses_offered,
    ROUND(AVG(gpa), 2) AS university_average_gpa,
    MIN(gpa) AS lowest_gpa,
    MAX(gpa) AS highest_gpa
FROM students;

total_students	students_with_phone	unique_courses	avg_gpa	lowest_gpa	highest_gpa
4	3	3	3.80	3.68	3.92

Next, let us learn how to compute these metrics for specific categories using the SQL GROUP BY Clause!

Frequently Asked Questions (FAQ)

1. What is the difference between COUNT(*) and COUNT(column_name)?

COUNT(*) counts every row in the result set regardless of whether columns contain NULL. COUNT(column_name) counts only rows where the specified column is NOT NULL.

2. How does COUNT(DISTINCT column_name) work?

COUNT(DISTINCT column_name) eliminates duplicate values and returns the count of unique, non-null values present in that column.

3. Can you combine standard columns with aggregate functions without a GROUP BY?

No! Writing 'SELECT department, AVG(salary) FROM employees' will raise an SQL syntax error (e.g., 'not a single-group group function') because an aggregate collapses multiple rows into one, conflicting with individual un-aggregated column rows. You must use GROUP BY.