Aggregates: COUNT, SUM, AVG, MIN and MAX

Every query so far returned one result row per table row (or per matched pair). An aggregate function does the opposite: it reads many rows and returns one value that summarises them.

1SELECT COUNT(*)   AS Products,2       SUM(Stock) AS UnitsInStock,3       MIN(Price) AS Cheapest,4       MAX(Price) AS Dearest,5       AVG(Price) AS AveragePrice6FROM dbo.Products;

Products  UnitsInStock  Cheapest  Dearest  AveragePrice
--------  ------------  --------  -------  ------------
      12          1422      4.10    89.00     22.254166

What COUNT counts

COUNT(*) counts rows. COUNT(column) counts the rows where that column has a value, so it skips NULLs. COUNT(DISTINCT column) counts the different values:

1SELECT COUNT(*)             AS Customers,2       COUNT(City)          AS WithCity,3       COUNT(DISTINCT City) AS Cities,4       COUNT(Email)         AS WithEmail,5       COUNT(Phone)         AS WithPhone6FROM dbo.Customers;

Warning: Null value is eliminated by an aggregate or other SET operation.

Customers  WithCity  Cities  WithEmail  WithPhone
---------  --------  ------  ---------  ---------
        8         7       4          6          5

The warning above the result is SQL Server telling you it ignored some NULLs. It is not an error, and SSMS shows it on the Messages tab.

Every aggregate except COUNT(*) skips NULLs

SUM, AVG, MIN and MAX ignore NULLs, as if those rows were not there. That is usually what you want, but it changes the meaning of AVG. Here a table of delivery charges has one charge not yet known:

1CREATE TABLE #Charges (OrderId int, Charge decimal(6,2) NULL);2INSERT INTO #Charges VALUES (1, 5.00), (2, 10.00), (3, NULL), (4, 9.00);3 4SELECT AVG(Charge)              AS AvgKnown,5       SUM(Charge) / COUNT(*)   AS AvgOverAll,6       AVG(ISNULL(Charge, 0))   AS AvgNullAsZero7FROM #Charges;

Warning: Null value is eliminated by an aggregate or other SET operation.

AvgKnown  AvgOverAll  AvgNullAsZero
--------  ----------  -------------
8.000000    6.000000       6.000000

AVG(Charge) divides 24 by the 3 known charges. Dividing by all 4 rows, or treating the missing charge as 0, gives 6. Decide which question you are asking: “the average of the charges we know” or “the average per order”.

The integer AVG trap

AVG returns the same type as its input. Stock is an int, so the average is an int too, and the fraction is thrown away:

1SELECT SUM(Stock)                      AS Total,2       COUNT(*)                        AS Products,3       AVG(Stock)                      AS IntAverage,4       AVG(CAST(Stock AS decimal(9,2))) AS RealAverage5FROM dbo.Products;

Total  Products  IntAverage  RealAverage
-----  --------  ----------  -----------
 1422        12         118   118.500000

Aggregates over a filter or a join

WHERE runs before the aggregate, so it picks which rows are summarised. Joins work the same way. This totals the value of every shipped order line:

1SELECT COUNT(*)                     AS Lines,2       COUNT(DISTINCT o.OrderId)    AS Orders,3       SUM(oi.Quantity * oi.UnitPrice) AS Revenue4FROM dbo.Orders AS o5JOIN dbo.OrderItems AS oi ON oi.OrderId = o.OrderId6WHERE o.Status = 'Shipped';

Lines  Orders  Revenue
-----  ------  -------
   14       7  1199.15

MIN and MAX also work on text (alphabetical order) and dates (earliest and latest):

1SELECT MIN(OrderDate) AS FirstOrder,2       MAX(OrderDate) AS LastOrder,3       DATEDIFF(day, MIN(OrderDate), MAX(OrderDate)) AS DaysTrading,4       MIN(Status)    AS FirstStatusAlphabetically5FROM dbo.Orders;

FirstOrder  LastOrder   DaysTrading  FirstStatusAlphabetically
----------  ----------  -----------  -------------------------
2024-02-03  2024-06-12          130  Cancelled

When there are no rows

An aggregate over zero rows still returns one row. COUNT says 0, but SUM, AVG, MIN and MAX have nothing to work with and return NULL. Plumbing has no products:

1SELECT COUNT(*) AS Products, SUM(Stock) AS Units, MAX(Price) AS Dearest2FROM dbo.Products3WHERE CategoryId = 7;

Products  Units  Dearest
--------  -----  -------
       0   NULL     NULL

Building a list with STRING_AGG

STRING_AGG (SQL Server 2017+) joins the values of many rows into one string, with a separator. WITHIN GROUP (ORDER BY …) sets the order of the list:

1SELECT STRING_AGG(Name, N', ') WITHIN GROUP (ORDER BY Name) AS OutOfStock2FROM dbo.Products3WHERE Stock = 0;

OutOfStock
--------------------------------
Safety goggles, Wall plugs (100)

Aggregates and plain columns do not mix

An aggregate collapses all rows into one, so a plain column has no single value to show beside it. Which product name should go next to MAX(Price)? SQL Server refuses to guess:

1SELECT Name, MAX(Price) AS Dearest2FROM dbo.Products;

Msg 8120, Level 16, State 1, Line 1
Column 'dbo.Products.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

There are two fixes, depending on the question. If you want one result per category, that is GROUP BY, the next lesson. If you want the name of the dearest product, find the maximum first and then the row that has it, which is a subquery, two lessons on.

1SELECT COUNT(*)                        AS Lines,2       SUM(oi.Quantity)                AS Items,3       SUM(oi.Quantity * oi.UnitPrice) AS OrderValue,4       STRING_AGG(p.Name, N', ') WITHIN GROUP (ORDER BY p.Name) AS Products5FROM dbo.OrderItems AS oi6JOIN dbo.Products AS p ON p.ProductId = oi.ProductId7WHERE oi.OrderId = 4;

Lines  Items  OrderValue  Products
-----  -----  ----------  -------------------------------------
    2     46      524.00  Plywood sheet 18 mm, Timber 2x4 2.4 m