Implementing Aggregates and the Group By Clause

Aggregate functions perform calculations on a data set and return a single value. This can be incredibly useful when reporting statistical data as it can be plotted within a returning result set and displayed within a customizable report template.

See below for a list of the most common aggregate functions available in SQL. What has to be noted is that there are more aggregate functions however the ones listed here I have used and would use the most.

AVG 

Returns the average of all values in the data set. An example of this is:

Executing the query above returns:

The query written displays all categories of items and shows the average price of items within each category.

COUNT

Returns the number of values in the data set (including NULL values) as an integer. COUNT DISTINCT excludes NULL values. An example of this is:

Executing the query above returns:

The query written displays all categories of items and shows the number of items within each category. For the purpose of this tutorial, I linked 100 items into each category.

MAX

Returns the highest value within the data set. An example of this is:

Executing the query above returns:

The query written displays the highest priced item within each category.

MIN

Returns the lowest value within the data set. An example of this is:

Executing the query above returns:

The query written displays the lowest priced item within each category.

SUM

Returns the total values within the data set. An example of this is:

Executing the query above returns:

The query written displays the total price of all contents within each category.

STDEV

Returns the standard deviation of the total values within the data set. An example of this is:

Executing the query above returns:

The Group By Clause

The GROUP BY clause is used when you have queries with aggregate functions in them. The GROUP BY clause allows for the definition of subtotals from the aggregate data, as you can see from the examples above. If you simply type: 

The error message you would receive is: 

Along with the GROUP BY clause, you can also add on the WITH ROLLUP and the WITH CUBE operators to provide grand totals and subtotals based on columns in the GROUP BY statement. 

An example of a WITH ROLLUP operator is as follows: 

When running the query above, the following result set is displayed: 

In the example shown, the ItemCategoryID column has been sorted ascending automatically and a new NULL row has appeared. This is what contains all of the grand totals for each column. The average over all item categories is £508.41, the overall maximum priced item is £999.53 and the overall minimum priced item is £2.15. The total worth of every item within each category is £508,413.25. This can be incredibly useful when producing an end of year report to state the number of assets a company has, the amount of stock they have sold and the amount of stock they have remaining to sell. 

Unfortunately, the WITH CUBE operator requires a larger database with more complicated table relationships in order to show a reasonable working example. However I visualize it as a 3-Dimensional operator that will allow you to work out the totals for Subcategories of the Item Categories and displays them in their own row too, similar to the WITH ROLLUP operator.