Aggregate Functions

Aggregate Functions

What it means

Aggregate functions compute a single result value from a set of rows — SUM, AVG, COUNT, MIN, MAX. Used alone, they collapse an entire result set into one row; combined with GROUP BY, they compute one result per group instead.

Examples

SELECT COUNT(*) AS total_orders FROM Orders;

SELECT SUM(Amount) AS total_revenue, AVG(Amount) AS avg_order FROM Orders;

SELECT MIN(OrderDate) AS first_order, MAX(OrderDate) AS last_order FROM Orders;

-- Combined with GROUP BY: one result per customer instead of one overall
SELECT CustomerId, COUNT(*) AS order_count, SUM(Amount) AS total_spent
FROM Orders
GROUP BY CustomerId;

Common mistake

Mixing aggregate and non-aggregate columns in the same SELECT without a GROUP BY — SQL Server rejects this, since it can’t determine which individual row’s value to show alongside a value computed across many rows.

-- Error: Column 'Orders.CustomerId' is invalid in the select list because it
-- is not contained in either an aggregate function or the GROUP BY clause.
SELECT CustomerId, SUM(Amount) FROM Orders;

-- Fix: add GROUP BY for every non-aggregated column
SELECT CustomerId, SUM(Amount) FROM Orders GROUP BY CustomerId;

Quick practice

1. What does COUNT(*) return?

   Answer

   The number of rows in the result set, including rows with NULL values in any column.

2. Why can’t you select a plain column alongside SUM(Amount) without also grouping by it?

   Answer

   Because SUM(Amount) collapses many rows into one value, but a plain column reference would need to pick a value from one specific row — SQL Server has no way to decide which, so it requires you to either aggregate it too or include it in GROUP BY.

3. Does COUNT(ColumnName) count NULL values in that column?

   Answer

   No — COUNT(ColumnName) only counts rows where that specific column is non-NULL, unlike COUNT(*).