Lesson 6 — Querying Data

Aggregate Functions

Aggregate functions calculate a single summary value — a count, sum, average, minimum, or maximum — across a set of rows.

The Five Core Aggregates

These five functions cover the vast majority of summary calculations you'll need, and are almost always paired with GROUP BY when you want a summary per category rather than for the whole table (covered in the next lesson).

T-SQL — Aggregate functions 
SELECT COUNT(*) AS TotalOrders FROM Orders;
SELECT SUM(Total) AS RevenueSum FROM Orders;
SELECT AVG(Total) AS AverageOrderValue FROM Orders;
SELECT MIN(Total) AS SmallestOrder FROM Orders;
SELECT MAX(Total) AS LargestOrder FROM Orders;

Common Mistakes

  • Using COUNT(ColumnName) expecting it to behave like COUNT(*) — COUNT(ColumnName) ignores NULL values in that column, which can produce a different (usually smaller) result.
  • Trying to select a non-aggregated column alongside an aggregate function without GROUP BY, which SQL Server will reject.
  • Forgetting that AVG and SUM ignore NULL values automatically, which can subtly skew results if NULLs represent "zero" in your data model.
  • Confusing COUNT(*) (counts all rows) with COUNT(DISTINCT column) (counts unique non-null values).

Professional Tip

Use COUNT(*) when you want the raw number of rows regardless of NULLs, and COUNT(ColumnName) specifically when you want to count how many rows have a non-null value in that particular column — the difference matters more often than it first appears.

Your Turn

Write queries to find the total number of customers, the average order value, and the highest single order total in an Orders table.

Mini Quiz

What is the key difference between COUNT(*) and COUNT(ColumnName)?