Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

SQL Basics

Ordering
Eliminating duplicate results
Aggregation
Grouping
19. Group by a few columns
HAVING: filtering and ordering groups
Let's practice

Instruction

Nice work.

There's one more thing about GROUP BY that we want to discuss. Sometimes we want to group the rows by more than one column. Let's imagine we have a few customers who place tons of orders every day, so we would like to know the daily sum of their orders.

SELECT
  customer_id,
  order_date,
  SUM(total_sum)
FROM orders
GROUP BY customer_id, order_date;

As you can see, we group by two columns: customer_id and order_date. We select these columns along with the function SUM(total_sum).

Remember: in such queries each column in the SELECT part must either be used later for grouping or it must be used with one of the functions.

To better understand the issue, take a look at the following table (expand the column or scroll the table horizontally if you need to):

order_id customer_id order_date ship_date total_sum customer_id order_date SUM(total_sum)
16 6 2015-03-28 2015-03-29 230.54 6 2015-03-28 2906.19
17 6 2015-03-28 2015-03-30 321.42
18 6 2015-03-28 2015-03-30 2354.23
19 6 2015-03-29 2015-03-30 4224.21 6 2015-03-29 10788.06
20 6 2015-03-29 2015-03-30 2314.32
21 6 2015-03-29 2015-03-31 124.21
22 6 2015-03-29 2015-03-31 4125.32
23 6 2015-03-30 2015-04-03 645.23 → 6 2015-03-30 10504.42
24 6 2015-03-30 2015-04-05 7543.56
25 6 2015-03-30 2015-04-05 2315.63
26 7 2015-04-02 2015-04-05 523.98 7 2015-04-02 9580.42
27 7 2015-04-02 2015-04-06 523.13
28 7 2015-04-02 2015-04-07 8533.31
29 7 2015-04-03 2015-04-07 4245.64 7 2015-04-03 4245.64

Note: It makes no sense to select any other column. For example, each order on the very same day by the very same customer can have a different shipping date. If you wanted to select the column ship_date in this case, the database wouldn't know which shipping date to choose for the whole group, so it would throw an error.

Exercise

Find the average salary for each employee. Show the last name, the first name, and the average salary. Group the table by the last name and the first name.

Stuck? Here's a hint!

Type:

SELECT
  last_name,
  first_name,
  AVG(salary)
FROM employees
GROUP BY last_name, first_name;