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_dateitemitems_purchased
2011-03-25 00:00:00.000Wireless Mouse2
2011-03-25 00:00:00.000Wireless Mouse5
2011-03-25 00:00:00.000MacBook Pro1
2011-04-01 00:00:00.000Paper Clips20
2011-04-01 00:00:00.000Stapler3
2011-04-01 00:00:00.000Paper Clips15
2011-05-15 00:00:00.000DVD player3
2011-05-15 00:00:00.000DVD player8
2011-05-15 00:00:00.000Stapler5
2011-05-16 00:00:00.000MacBook Pro2

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_dateitemTotal Items
2011-03-25 00:00:00.000Wireless Mouse7
2011-03-25 00:00:00.000MacBook Pro1
2011-04-01 00:00:00.000Paper Clips35
2011-04-01 00:00:00.000Stapler3
2011-05-15 00:00:00.000DVD player11
2011-05-15 00:00:00.000Stapler5
2011-05-16 00:00:00.000MacBook Pro2

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!

Hiring? Job Hunting? Post a JOB or your RESUME on our JOB BOARD >>

Subscribe to our newsletter for more free interview questions.

  • MB Reddy

    superb explanation, very easy to understand for beginners and self learners