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.

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:

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!