Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
Ordering
Eliminating duplicate results
Aggregation
Grouping
17. 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 is used together with many other functions. Take a look:

SELECT
  customer_id,
  MAX(total_sum)
FROM orders
GROUP BY customer_id;

We've replaced COUNT(*) with MAX(total_sum). 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 highest value for each customer.

Exercise

Show all departments together with their lowest and highest salary in 2014.

Stuck? Here's a hint!

Type:

SELECT
  department,
  MIN(salary),
  MAX(salary)
FROM employees
WHERE year = 2014
GROUP BY department;