Consider the following table, orders.

buyer             productName     purchaseDate   qtyPurchased     pricePaid
-------------     --------------- --------------- -----------     ---------
Shannon Lowder    pencil          1/1/2000         1              .25
Shannon Lowder    paper           1/1/2000         2              1.00
Shannon Lowder    Mountain Dew    1/1/2000         1              1.25
Shannon Lowder    pencil          1/5/2000         1              .25
Shannon Lowder    pencil          1/7/2000         1              .25
Shannon Lowder    Mountain Dew    1/10/2000        1              1.25
Shannon Lowder    Mountain Dew    1/11/2000        1              1.25
Shannon Lowder    Mountain Dew    1/12/2000        1              1.25
Shannon Lowder    Mountain Dew    1/13/2000        1              1.25
Shannon Lowder    Mountain Dew    1/14/2000        1              1.25

How would we find out the total number of pencils I purchased?

You could do that with a WHERE clause.

What if I wanted to see a quantity of each product I ordered?

Now that’s different.

If you need to use an aggregate function and break down that aggregate by categories (or groups), then you’re going to need to learn the GROUP BY clause.  This clause will help you summarize data by these groups.  So basically I’m going to teach you an add-on to the aggregate functions lesson.

Let’s dive right in.

Problem 1: Show me the total quantities purchased, broken down by product.

SELECT
     productName
   , SUM(qty) as [total quantity]
FROM orders
GROUP BY
   productName

productName    total quantity
------------    --------------
pencil		     3
paper		     2
Mountain Dew         6

Pretty easy, right? You can group by any one column or multiple columns. Grouping by multiple columns is a fundamental for most reports you’ll be asked to write. You will eventually be asked a question like:

Problem 2: Show me a Report of Which Products Were Sold, by Month?

At first this seems more difficult, but really, all you’ll have to do is add an additional column to your SELECT and GROUP BY list. I would like to take an aside here, and point out, sometimes business requirements aren’t entirely clear. This example is a great case to consider ambiguity in a request. Is product name the “main” or first group, or is the month, the first group? In this case, we’re going to use productName as the first group.

SELECT
     productName
   , MONTH(purchaseDate) as [Month]
   , SUM(qty) as [total quantity]
FROM orders
GROUP BY
     productName
   , MONTH(purchaseDate)

productName     Month	total quantity
------------    -----   --------------
pencil		1	3
paper		1	2
Mountain Dew    1       6

Since all the data in my example happened in January 2000, all the counts from before appear as they did before. This time we just have the extra column in the output. This is also the time where I will point out you can use functions in your GROUP BY clause, and as long as it’s not an aggregate function (like SUM, MAX, MIN, etc) you can use it!

Summary

The GROUP BY clause is a fundamental part of reporting. Experiment with it, and become comfortable with it. If you have any questions, send them in! I’m here to help you learn all about SQL.

Previous: Summarizing Data

Next: Sub Queries