Aggregation functions are commonly used in SQL Server to perform calculations on a set of values and return a single value as the result. The most familiar way to use aggregation functions is with the GROUP BY clause, where you can group rows based on certain criteria and apply an aggregate function to each group.

For example, you can use the SUM function to calculate the total amount of grants in a table:

SELECT SUM(Amount) AS TotalGrants
FROM [Grant]

However, when using the GROUP BY clause, you are limited to including only aggregated data in your result set. If you try to select individual columns along with the aggregated function without including them in the GROUP BY clause, you will encounter an error.

But what if you want to show the total amount next to every record in the table, or just one record? This is where the OVER() clause comes in handy.

The OVER() clause allows you to apply an aggregation function, such as SUM, to all rows of a query without the need for a GROUP BY clause. It provides a way to calculate the aggregate value for each row, rather than for each group.

Here’s an example of using the SUM function with the OVER() clause:

SELECT *, SUM(Amount) OVER() AS TotalGrants
FROM [Grant]

In this example, the SUM function is applied to all rows of the [Grant] table, and the total amount of grants is displayed next to each record.

You can also use the PARTITION BY clause with the OVER() clause to further divide the rows into partitions and calculate the aggregate value for each partition. This can be useful when you want to compare individual values with their respective partitions.

For example, if you want to compare each grant amount with the total amount of all grants, you can use the following query:

SELECT *, SUM(Amount) OVER() AS TotalGrants, Amount / SUM(Amount) OVER() * 100 AS PercentageOfTotal
FROM [Grant]

In this query, the SUM function is applied to all rows of the [Grant] table, and the total amount of grants is calculated. The grant amount is then divided by the total amount and multiplied by 100 to get the percentage of the total.

By using the OVER() clause, you can easily perform calculations and comparisons on individual values with respect to the entire dataset, without the need for complex subqueries or temporary tables.

Understanding aggregation functions and the OVER() clause in SQL Server can greatly enhance your ability to analyze and manipulate data. Whether you need to calculate totals, compare values, or perform other calculations, these powerful features provide a flexible and efficient solution.

For more in-depth information and examples on SQL Server concepts, you can refer to the book “Beginning SQL 2012 – Joes 2 Pros Volume 2” by Joes2Pros.