GROUP BY statements use various aggregate functions to combine rows and look at aggregated information.
Aggregate functions used below: MIN, MAX, AVG, COUNT, SUM.
The calls below are made a hypothetical transaction database with columns first_name, last_name, customer_id, employee_id, amount, and transaction_quality.
GROUP BY with SUM
Return the total amount paid by each customer:
SELECT customer_id, SUM(amount)
GROUP BY customer_id;
GROUP BY with COUNT
Return the number of transactions by transaction_quality:
SELECT transaction_quality, COUNT(transaction_quality)
GROUP BY transaction_quality;
GROUP BY with AVG and ROUND
Return the average purchase price (rounded to 2 decimal places) for each customer:
SELECT customer_id, ROUND(AVG(amount),2)
ORDER BY customer_id;
GROUP BY with SUM and ORDER BY
Return the five employees who have sold the most (by amount rather than quantity of transactions):
SELECT employee_id, SUM(amount)
GROUP BY employeee_id
ORDER BY SUM(amount) DESC