Define Aggregate Queries During SQL Data Selection

An aggregate query^[1] is one that contains a Transact-SQL function performing a calculation on a set of values, returning that calculation as a single value. For example, if you wanted to know how much an organization pays out in salaries, you could ask for the total of every value in the Salary column of the Employees table, a single number summarizing many rows. Aggregate functions are instrumental in data analysis, letting you summarize data across rows directly from the database rather than in application code. They are frequently used in conjunction with the GROUP BY clause.

• Summarizing data. Functions such as SUM, AVG, COUNT, MAX, and MIN compute summary statistics, SUM(column_name) for a total, AVG(column_name) for an average, and so on.
• Grouping data. The GROUP BY clause groups rows that share the same value in specified columns into summary rows, letting you see, for example, total sales per region or per month.
• Filtering aggregated data. The HAVING clause filters groups after aggregation has been applied, the way WHERE filters individual rows before aggregation.
• Creating subtotals and totals. Used without GROUP BY, an aggregate function summarizes the entire result set as a single total. Used with GROUP BY, you get a subtotal per group, and WITH ROLLUP or WITH CUBE can add grand totals on top of those subtotals.
• Working with distinct values. The DISTINCT keyword can be combined with an aggregate, as in COUNT(DISTINCT column_name), to count only unique values in a column.
• Analytical and window functions. Functions such as ROW_NUMBER(), RANK(), and DENSE_RANK() perform calculations across a set of rows related to the current row, typically paired with an OVER clause to define partitioning and ordering.
• Combining aggregated data with detailed data. A subquery or common table expression (CTE) can join aggregated results back to the original, row-level data, enriching detail with summary statistics in the same result set.

Ten aggregate functions have long been part of Transact-SQL:

1. AVG - returns the average of all values in a group
2. COUNT - returns the number of rows for which the specified criteria apply
3. GROUPING - used with OLAP-style aggregation (ROLLUP, CUBE, GROUPING SETS) to indicate whether a row's value is an actual data value or a generated summary row
4. MAX - returns the maximum value in a group
5. MIN - returns the minimum value in a group
6. SUM - returns the sum of all values in a group
7. STDEV - returns the statistical standard deviation
8. STDEVP - returns the statistical standard deviation for a population of values
9. VAR - returns the statistical variance
10. VARP - returns the statistical variance for a population of values

SQL Server 2025 adds a new entry to this list:

11. PRODUCT - calculates the product (multiplication) of a set of values, filling a gap the other ten functions never covered: there had never been a built-in way to multiply a column's values together the way SUM adds them.

SQL Server 2025 also adds two aggregate functions specifically for building JSON directly from a query: JSON_OBJECTAGG, which constructs a JSON object from an aggregation, and JSON_ARRAYAGG, which constructs a JSON array from one. Both let a query produce ready-to-use JSON output without a separate formatting step.

A couple of attributes are worth remembering about these functions. Each one ignores NULL values when evaluating the column it is given, with one nuance: COUNT(column_name) ignores nulls in that column just like the others, but COUNT(*) counts every row regardless of whether any column in it is null, since it is counting rows rather than evaluating a specific column's values.

Each aggregate function follows the same general syntax:

AggregateFunction(column_name or expression) AS alias_name

Aggregate functions are frequently used alongside joins, summarizing data that spans more than one table. Consider these two tables:

The following query joins these tables and totals each customer's orders:

SELECT c.CustomerName, SUM(o.Amount) AS TotalAmount
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName;

• SELECT c.CustomerName, SUM(o.Amount) AS TotalAmount retrieves each customer's name, along with the SUM of every order amount tied to them.
• FROM Customers c names the primary table and aliases it c.
• JOIN Orders o ON c.CustomerID = o.CustomerID joins the Orders table (aliased o) wherever CustomerID matches, combining each customer with their own orders.
• GROUP BY c.CustomerName groups the results by customer, so SUM calculates a total for each one individually rather than one grand total across everyone.

This kind of query is exactly what generates a report like "total sales per customer." Swapping the aggregate function changes what the report shows without changing the query's structure at all. Counting orders per customer instead of summing their value, for example, needs only one change:

SELECT c.CustomerName, COUNT(o.OrderID) AS NumberOfOrders
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName;

Using aggregate functions together with joins is what lets you perform calculations across related tables in a single statement, rather than pulling detailed rows back to application code and summarizing them there.

In the next lesson, you will learn how to use aggregate functions in your own queries in more depth.

[1] Aggregate: a query or part of a query that performs a mathematical calculation, such as summing, on one or more columns, returning a single value.