Techie BabajiLearn to Code. Build Real Skills.
SQL Server · Combining Data

Aggregate Functions

Aggregate functions turn many values into one: a count, a total, an average. They are the building blocks of every report.

Intermediate 5 min Lesson 17 of 31Examples run on SQL Server 2022 and Azure SQL Database.
Before you start:SELECT
0/31 in SQL Server
On this page · 5 min · Intermediate

On this page

  1. In simple words
  2. Learning objectives
  3. Explanation
  4. Code example
  5. Step by step
  6. Real-world use
  7. Common mistakes
  8. Best practices
  9. Remember this
  10. Practice exercise
  11. Mini quiz
  12. Related topics
  13. My notes
Reading time
5 min
Difficulty
Intermediate
Updated
Oct 27, 2026

Related tutorials

Interview questions

In simple words#

Aggregates are a calculator for whole columns: how many (COUNT), total (SUM), average (AVG), smallest (MIN), largest (MAX).

COUNT(*)
Number of rows.
SUM / AVG
Total / average of a column.
NULLs
Ignored by SUM, AVG, MIN, MAX.

Learning objectives#

  • Use COUNT, SUM, AVG, MIN and MAX
  • Explain COUNT(*) vs COUNT(column)
  • Understand how NULL affects aggregates
  • Combine strings with STRING_AGG

Explanation#

Aggregates work over all rows (or each group when used with GROUP BY) and return one value.

COUNT(*) counts rows. COUNT(column) counts only non-NULL values. SUM, AVG, MIN and MAX ignore NULLs too.

AVG of an integer column returns an integer in SQL Server; cast to DECIMAL for a precise average.

STRING_AGG(column, separator) joins values into one string; WITHIN GROUP (ORDER BY …) controls the order.

Code example: Summaries and NULL behaviour#

SQLSummaries and NULL behaviour Illustrative
SELECT COUNT(*)   AS Products,
       MIN(Price) AS Cheapest,
       MAX(Price) AS Priciest,
       STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name) AS AllNames
FROM Products;


SELECT COUNT(*) AS AllRows, COUNT(x) AS NonNull, AVG(x) AS Average
FROM (VALUES (10), (NULL), (20)) AS t(x);
Expected output
Products  Cheapest  Priciest  AllNames
6         20.00     55000.00  Chair, Desk, Laptop, Mouse, Notebook, Pen

AllRows  NonNull  Average
3        2        15

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

  1. Without GROUP BY, the aggregates cover the whole table.
  2. STRING_AGG builds an alphabetical, comma-separated list of names.
  3. In the second query, COUNT(*) counts all three rows, COUNT(x) skips the NULL.
  4. AVG(x) averages only 10 and 20 — the NULL is ignored, giving 15.

Real-world use case#

A dashboard header showing “Total orders”, “Revenue” and “Average order value” is a single aggregate query.

Common mistakes#

Expecting NULL to count as zero

AVG ignores NULLs. If missing values should count as 0, use AVG(ISNULL(x, 0)).

Integer averages

AVG of INT columns truncates (AVG of 1 and 2 is 1). Use AVG(CAST(x AS DECIMAL(10,2))).

Best practices#

  • Use COUNT(*) to count rows
  • Decide explicitly how NULLs should be treated
  • Cast integers before averaging
  • Alias every aggregate column

Remember this#

COUNT(*) counts rows; COUNT(col) skips NULLs.

SUM and AVG ignore NULLs.

Aggregate + GROUP BY = totals per group.

Practice exercise#

Your task: Return the total quantity of items sold across all orders.

Show solution
SQL Illustrative
SELECT SUM(Qty) AS ItemsSold FROM OrderItems;  -- 1+2+1+10+5+2 = 21

Mini quiz#

Aggregate Functions quizPredict the output Question 1 of 1

A column has values 5, NULL, NULL. What does COUNT(column) return?

Score so far: 0/0

Want more? Practise all SQL Server MCQs · SQL Server interview questions

My notes#

Saved in this browser.

Progress is saved in this browser. Sign in to sync.