# SQL 101 – GROUP BY

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.

## By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.