Learning SQL, Part 5 - Aggregations and Group By Statements

This is the fifth article in the learning SQL series:

• Part 1: Setup your environment
• Part 2: Basic Select Statements
• Part 3: Where Statements
• Part 4: More Where Statements
• Part 5: Group By Statements (this article)
• Part 6: Joins

Aggregating in SQL: GROUP BY Statements

Previous articles reviewed how to select relevant records from tables and apply filter conditions as needed. However, many times in reporting you want to aggregate results and perform additional calculations - such as counting the number of rows for a group or adding subtotals. This is where the GROUP BY statement comes in.

For this example, a new table will be used - FactResellerSales - in order to demonstrate how to aggregate sales dollars by date. The table has an OrderDate column that we’d like to see the total ExtendedAmount displayed for each day.

To get the subtotal by days, we’ll use the SUM aggregation function and GROUP BY on OrderDate. The general idea is to aggregate a metric using one of the various aggregation functions available and all the other columns (typically descriptive attributes such as dates, products, etc) will be included in both the select and group by statements.

SELECT
	 OrderDate
	,SUM(ExtendedAmount) as [ExtendedAmountTotal]
FROM [AdventureWorksDW2017].[dbo].[FactResellerSales]
GROUP BY
	OrderDate
ORDER BY
	OrderDate

Other Aggregate Functions

While SUM is the most commonly used aggregation function, there are many more that can be used. A list of aggregation functions can be viewed on the Microsoft documentation page. Max, Min, Avg, and Count are some of more frequently used functions in addition to Sum.

Next Steps

Try some of the above functions (in addition to Sum) on your own to get a feel for using these. You can even combine with previous concepts - for example COUNT(DISTINCT LastName) from the DimCustomer table will give you a distinct/unique count of customer last names.

Check out additional articles in this series here:

• Part 1: Setup your environment
• Part 2: Basic Select Statements
• Part 3: Where Statements
• Part 4: More Where Statements
• Part 5: Aggregations and Group By Statements (this article)
• Part 6: Joins