Define Aggregate Queries during SQL Data Selection

In SQL Server 2019, aggregate queries are used to perform calculations on a set of values to return a single scalar value. They are instrumental in data analysis, especially when you need to summarize or aggregate data across rows. Here's how they are commonly used:

1. Summarizing Data: Aggregate functions like `SUM()`, `AVG()`, `COUNT()`, `MAX()`, and `MIN()` are used to compute summary statistics. For instance, you might use `SUM(column_name)` to calculate the total value of a numeric column or `AVG(column_name)` to find the average value.
2. Grouping Data: Aggregate queries often use the `GROUP BY` clause to group rows that have the same values in specified columns into summary rows. For example, you could group sales data by region or by month to see the total sales per region or month.
3. Filtering Aggregated Data: The `HAVING` clause is used in conjunction with `GROUP BY` to filter groups or aggregates based on a specified condition. Unlike the `WHERE` clause that filters rows before aggregation, `HAVING` filters after the aggregation has been applied.
4. Creating Subtotals and Totals: Aggregate functions can be used with or without the `GROUP BY` clause. When used without `GROUP BY`, an aggregate function will summarize all the data selected by the query, effectively creating a total. With `GROUP BY`, you can create both subtotals (for each group) and totals (using `WITH ROLLUP` or `WITH CUBE`).
5. Working with Distinct Values: Aggregate functions can also work on distinct values within a column using the `DISTINCT` keyword, for example, `COUNT(DISTINCT column_name)` to count the number of unique values in a column.
6. Analytical and Window Functions: SQL Server 2019 supports advanced analytical functions like `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, and others that can perform calculations across a set of rows related to the current row. These are often used in conjunction with over clauses to specify the partitioning and ordering of the data for the calculation.
7. Combining Aggregate Data with Detailed Data: Sometimes, you may need to join aggregated data back to the original dataset to enrich the detailed data with summary statistics. This can be achieved through subqueries or common table expressions (CTEs).

Aggregate queries are a powerful feature in SQL Server 2019, enabling complex data summarization, analysis, and reporting directly from the database without requiring additional processing in application code.

An aggregate query^[1] is one that contains a specific Transact-SQL function that performs a calculation on a set of values. This calculation is returned by the query as a single value. For example, if you wanted to know how much you are paying out in salaries, you could ask for the total of all values in the Salary column in the Employees table. Aggregates are used in conjunction with the GROUP BY clause.

There are ten aggregate functions within the Transact-SQL language. They are:

1. AVG – Returns the average of all values in a group of values
2. COUNT – Returns the number of rows in the table(s) for which the criteria applies in a group of values
3. GROUPING – Used with On-line Analytical Processing (OLAP) functionality.
4. MAX – Returns the maximum value in a group of values
5. MIN – Returns the minimum value in a group of values
6. SUM – Returns the summarization, or addition, of all values in a group of values
7. STDEV – Returns the statistical standard deviation
8. STDEVP – Returns the statistical standard deviation for a population of values
9. VAR – Returns the statistical standard variance
10. VARP – Returns the statistical standard variance for a population of values

There are a couple of attributes to remember about these functions. Each of the functions ignore null values, except for the COUNT function. Also, each of the functions uses the following syntax:

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

[1] Aggregate: A query or part of a query that performs mathematical summing on one or more columns.