From the course: Querying Microsoft SQL Server 2022

Understand record grouping - SQL Server Tutorial

From the course: Querying Microsoft SQL Server 2022

Start my 1-month free trial Buy for my team

Understand record grouping

“

- [Instructor] The queries that we've explored so far have all returned a number of rows from the database. We can take a different look at the data by grouping those rows together based on common attributes in order to get summary details and other useful kinds of information out of the database. My favorite way to think about grouping records in a query is to imagine a standard deck of 52 playing cards. Each card in the deck will represent one row in a query's result. You can then take that deck of cards and separate them out by color. Make one pile for black cards and another pile for red cards. You've just created two groups from the original deck of cards based on their color. Or you could group the cards into four groups, one for each suit. That would give you a pile of club cards, spade cards, hearts, and diamond cards. Or you could group the cards based on their value. That would leave you with 13 groups with four cards each. You'd have four aces, four kings, four queens, and so on through the rest of the deck. That's three different ways that you could group the cards in a standard deck of playing cards and each one can give you a different insight into the makeup of the deck. Applying groups to the rows returned by a database query can do the same thing for understanding your data from different perspectives. In order to create groups in a query, you'll add a new clause to the select statement, the GROUP BY clause. The GROUP BY clause follows the FROM clause, and in it, you simply list the columns that you'd like to use for consolidating records. This will lump all of the rows that the query would have returned into buckets, like our individual piles of playing cards. Once that happens, you can then use aggregate functions to perform some calculations on the rows that are contained in each bucket. For instance, you can count up how many rows are in each group, or calculate the sum total, or find the minimum, maximum, or average value of all of the rows in each group. In the playing card example, we might have a query output that looks something like this. If we were to group the cards based on color and count the cards in each group with an aggregate function, the results grid would show a single row for each color and display the count of cards in each group. Or if we grouped by suit, the results would show four rows, one for each suit, and the counts would update. And if we group the query results by card value, then we wind up with 13 rows to the results like this. And that's how the GROUP BY clause works in a select query. It takes the standard query output and then consolidates rows based on a common value. Then, it provides aggregates, or summary statistics, across those groups.

Contents