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
19. Find min and max values in groups
HAVING: filtering and ordering groups
Let's practice

Instruction

Excellent! Of course, COUNT(*) isn't the only option. In fact, GROUP BY can be used together with many other functions. Take a look:

SELECT
  CustomerId,
  MAX(TotalSum) AS MaxCustomerSum
FROM Order
GROUP BY CustomerId;

We've replaced COUNT(*) with MAX(TotalSum). Can you guess what happens now?

That's right, instead of counting all the orders for specific clients, we'll find the order with the greatest value for each customer.

Exercise

Show all departments together with their lowest and highest salaries in 2014. Name the last two columns MinDepartmentSalary and MaxDepartmentSalary, respectively.

Stuck? Here's a hint!

Type:

SELECT
  Department,
  MIN(Salary) AS MinDepartmentSalary,
  MAX(Salary) AS MaxDepartmentSalary
FROM Employee
WHERE Year = 2014
GROUP BY Department;