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 in MS SQL Server

Ordering
Limiting the output
Eliminating duplicate results
Aggregation
Grouping
21. 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
  CustomerId,
  OrderDate,
  SUM(TotalSum) AS CustomerDailySum
FROM Order
GROUP BY CustomerId,
  OrderDate;

As you can see, we group by two columns: CustomerId and OrderDate. We select these columns along with the function SUM(TotalSum).

Here's an important thing to remember when working with GROUP BY: each column in the SELECT part must either be used later for grouping or be used in an aggregate function. 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 ShipDate in this case, the database wouldn't know which shipping date to choose for the whole group, so it would put just one random value in the result.

To better understand the issue, take a look at the following table:

OrderId CustomerId OrderDate ShipDate TotalSum (grouping) CustomerId OrderDate SUM(TotalSum) ShipDate
16 6 28-03-2015 29-03-2015 230.54 --> 6 28-03-2015 0 ?? 29-03-2015? 30-03-2015?
17 6 28-03-2015 30-03-2015 321.42 -->        
18 6 28-03-2015 30-03-2015 2354.23 -->        
19 6 29-03-2015 30-03-2015 4224.21 --> 6 29-03-2015 0 ??
20 6 29-03-2015 30-03-2015 2314.32 -->        
21 6 29-03-2015 31-03-2015 124.21 -->        
22 6 29-03-2015 31-02-2015 4125.32 -->        
23 6 30-03-2015 03-04-2015 645.23 --> 6 30-03-2015 0 ??
24 6 30-03-2015 05-04-2015 7543.56 -->        
25 6 30-03-2015 05-04-2015 2315.63 -->        
26 7 02-04-2015 05-04-2015 523.98 --> 7 02-04-2015 0 ??
27 7 02-04-2015 06-04-2015 523.13 -->        
28 7 02-04-2015 07-04-2015 8533.31 -->        
29 7 03-04-2015 07-04-2015 4245.64 --> 7 03-04-2015 0 ??

Exercise

Find the average salary for each employee. Show each employee's first and last name and average salary. Group the table by last name and first name. Name the aggregate column AvgSalary.

Stuck? Here's a hint!

Type:

SELECT
  LastName,
  FirstName,
  AVG(Salary) AS AvgSalary
FROM Employee
GROUP BY LastName,
  FirstName;