Ordering
Eliminating duplicate results
Aggregation
Grouping
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;
