COUNT, SUM, AVG, MIN, and MAX: Summarize a set
Sometimes we don’t need to look at each product separately. We want to know how many are active, how much their prices add up or what the minimum and maximum values of the catalog are.
Aggregation functions take multiple rows and produce a summarized value.
The idea
Each function answers a different question:
COUNT: count rows or values
COUNT(*)
COUNT(Columna)SUM: add numeric values
SUM(ColumnaNumerica)AVG: calculate the average
AVG(ColumnaNumerica)MIN: get the smallest value
MIN(Columna)MAX: get the largest value
MAX(Columna)Without GROUP BY, the query produces a single row for the entire filtered set.
Example
To summarize the prices of active products:
SELECT
COUNT(*) AS CantidadProductos,
SUM(p.Precio) AS SumaPrecios,
AVG(p.Precio) AS PrecioPromedio,
MIN(p.Precio) AS PrecioMinimo,
MAX(p.Precio) AS PrecioMaximo
FROM dbo.Producto AS p
WHERE p.Activo = 1;| QuantityProducts | SumPrices | AveragePrice | Minimum Price | Maximum Price |
|---|---|---|---|---|
| 5 | 2186.40 | 437.28 | 189.50 | 799.00 |
The filter is applied before the summary. The inactive external drive does not participate in any of the five calculations.
COUNT(*) and COUNT(Columna)
These ways don’t always return the same thing:
SELECT
COUNT(*) AS TotalClientes,
COUNT(c.Correo) AS ClientesConCorreo
FROM dbo.Cliente AS c;COUNT(*) counts rows. COUNT(c.Correo) counts only rows where Correo is not NULL. NULL represents an absent or unknown value; it is not the same as zero or an empty string.
If the table contains five clients and one does not have email, the result is:
| TotalClients | ClientsWithEmail |
|---|---|
| 5 | 4 |
Expressions within an aggregation
Functions can also summarize an expression. To calculate the amount before discount for all items:
SELECT
SUM(d.Cantidad * d.PrecioUnitario) AS ImporteBruto
FROM dbo.PedidoDetalle AS d;The multiplication is performed for each row and SUM then adds the results.
What should be checked
Except COUNT(*), aggregate functions ignore NULL values. That may be correct or it may hide missing data; It depends on the question.
The data type also matters. AVG follows rules associated with the input type, and division or multiplication can lose precision when we use integers or unsuitable types. In DECIMAL, precision is the total number of digits and scale is the number of digits after the decimal separator.
A JOIN can multiply rows before aggregation. If the total seems too high, first check the cardinality: the number of rows reaching SUM or COUNT.