The GROUP BY clause in MySQL is used to group rows that have the same values into summary rows, like “find the sum of sales for each product”. It works in conjunction with aggregate functions such as SUM, COUNT, AVG, etc.
Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s);
Example: Let’s say we have a table named “sales” containing data about sales made by different salespeople in different cities.
| salesperson | city | sale_amount |
|---|---|---|
| John | New York | 500 |
| Anna | London | 700 |
| Mary | Paris | 800 |
| John | New York | 300 |
| Anna | Paris | 400 |
| Mary | Paris | 600 |
If we want to find the total sales amount for each salesperson, we can use the following query:
SELECT salesperson, SUM(sale_amount) AS total_sales
FROM sales
GROUP BY salesperson;
This will give us the result:
| salesperson | total_sales |
|-------------|-------------|
| Anna | 1100 |
| John | 800 |
| Mary | 1400 |
The query groups the rows by “salesperson”, calculates the sum of “sale_amount” for each group, and returns the results in a summary table.
