Grouping

Episode #9 of the course Intro to SQL by Mike Schmitt

 

With the previous lesson, we began to see how aggregate functions may be useful. But what if we wanted those values broken down further? What if our online business wanted a breakdown of the customer base by address? Or wouldn’t it be helpful to get a breakdown of sales by item? We can do this! Let’s take a look at our Orders table, which includes the InventoryID of the item sold along with the quantity sold (CartCount):

We want to get a sum of the CartCount values using the SUM() function, but we also want the sum broken down per item. There are InventoryIDs 1, 2, 3, and 4, so we want the total for each of these. Here is the SQL syntax:

SELECT InventoryID, SUM(CartCount) FROM Orders GROUP BY InventoryID;

Notice we have added InventoryID to our selected columns and added GROUP BY InventoryID to the end of our statement. GROUP BY indicates which columns to break down the aggregated portion by. Our result set would then look like:

Look! From the result set, we just discovered what our hottest-selling item is!

Also note that you can include additional aggregates like AVG() or MAX() in a group by query or list additional fields from your table to group by.

And now you know how to group your result sets!

Explore on your own with an SQL Fiddle of today’s lesson!

 

Recommended book:

“Head First SQL: Your Brain on SQL–A Learner’s Guide” by Lynn Beighley

 

Share with friends