COUNT, SUM, AVG, MIN, and MAX

This lesson uses the unmodified DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Summarize a complete set of product prices with aggregate functions.

Run the example

SELECT COUNT(*) AS ProductCount,
       SUM(ListPrice) AS PriceSum,
       AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS LowestPrice,
       MAX(ListPrice) AS HighestPrice
FROM dbo.Products;

Read it step by step

1. FROM supplies all 15 products in the unmodified v1.0.0 dataset.
2. COUNT(*) counts rows; the other functions summarize non-NULL ListPrice values.
3. With no GROUP BY, the query produces one summary row.

Check the result

ListPrice is NOT NULL, so all 15 prices contribute. AVG on this decimal column returns 74.366666 at six fractional digits. That value is truncated; rounding the exact average to six places would give 74.366667.

Practice

Count active products and find their lowest and highest ListPrice.

SELECT COUNT(*) AS ActiveProducts, MIN(ListPrice) AS LowestPrice,
       MAX(ListPrice) AS HighestPrice
FROM dbo.Products
WHERE IsActive = 1;