COUNT, SUM, AVG, MIN and MAX

Turn rows into meaningful measures.

UnderstandStudy an examplePractise

Understand the concept

Aggregates summarise multiple rows. COUNT(*) counts rows; COUNT(Column) counts non-NULL values. SUM and AVG ignore NULL inputs. Over an empty set, COUNT returns zero while SUM/AVG can return NULL. Unknown and zero are not equivalent.

Sales amounts should use Quantity * UnitPrice from the order line. Product.Price is a current list price, not necessarily the historical sale price. Check AVG’s result type for integer inputs; cast to an appropriate decimal when a fractional mean is needed.

WHEN TO USE IT

Use it for counts, totals, averages and value ranges.

COMMON PITFALL

Computing historical revenue from current prices, or automatically treating NULL as zero.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT MIN(Price) AS Cheapest, MAX(Price) AS Highest FROM lab.Product;

The minimum price is 120 and the maximum is 30000.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Compare total customers with known email addresses.

Show the solution and explanation

Worked solution

SELECT COUNT(*) AS Customers, COUNT(Email) AS KnownEmails FROM lab.Customer;

There are 6 customers and 4 known emails. Denominator choice changes a ratio.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.