A SQL query that performs a calculation across a set of rows and returns a single summary result using aggregate functions such as SUM, COUNT, AVG, MIN, or MAX.
- Aggregate queries are fundamental to reporting, analytics, and monitoring: They are used to summarize transaction volumes, calculate averages, and identify outliers.
- In SQL Server, aggregate functions are typically used with GROUP BY: This enables summaries to be computed per category or partition.
- Window functions (OVER clause) extend aggregation capabilities: They compute results across a defined row set without collapsing output to a single row.
- Poorly written aggregate queries can cause significant performance issues: This is especially true on large tables without appropriate indexes or statistics.
- DBAs optimize aggregate query performance by: Reviewing execution plans, ensuring covering indexes exist, and using Query Store to track regressions.
- Related terms: GROUP BY, Window Functions, Query Optimization, Columnstore Index.
