In SQL, how and when would you do a group by with multiple columns? Also provide an example.

In SQL, the group by statement is used along with aggregate functions like SUM, AVG, MAX, etc. Using the group by statement with multiple columns is useful in many different situations – and it is best illustrated by an example. Suppose we have a table shown below called Purchases. The Purchases table will keep track of all purchases made at a fictitious store.

Purchases

purchase_date item items_purchased 2011-03-25 00:00:00.000 Wireless Mouse 2 2011-03-25 00:00:00.000 Wireless Mouse 5 2011-03-25 00:00:00.000 MacBook Pro 1 2011-04-01 00:00:00.000 Paper Clips 20 2011-04-01 00:00:00.000 Stapler 3 2011-04-01 00:00:00.000 Paper Clips 15 2011-05-15 00:00:00.000 DVD player 3 2011-05-15 00:00:00.000 DVD player 8 2011-05-15 00:00:00.000 Stapler 5 2011-05-16 00:00:00.000 MacBook Pro 2

Now, let’s suppose that the owner of the store wants to find out, on a given date, how many of each product was sold in the store. Then we would write this SQL in order to find that out:

select purchase_date, item, sum(items_purchased) as 
"Total Items" from Purchases group by item, purchase_date;

Running the SQL above would return this:

purchase_date item Total Items 2011-03-25 00:00:00.000 Wireless Mouse 7 2011-03-25 00:00:00.000 MacBook Pro 1 2011-04-01 00:00:00.000 Paper Clips 35 2011-04-01 00:00:00.000 Stapler 3 2011-05-15 00:00:00.000 DVD player 11 2011-05-15 00:00:00.000 Stapler 5 2011-05-16 00:00:00.000 MacBook Pro 2

Note that in the SQL we wrote, the group by statement uses multiple columns: “group by item, purchase_date;”. This allows us to group the individual items for a given date – so basically we are dividing the results by the date the items are purchased, and then for a given date we are able to find how many items were purchased for that date. This is why the group by statement with multiple columns is so useful!