MySQL GROUP BY
In MySQL, the GROUP BY clause is used with the SELECT statement to group the fetched data by one or more columns.
Syntax: To group the rows by values in multiple columns.
SELECT expressions FROM table_name GROUP BY columns;
Parameters:
expressions: It is used to specify the columns or calculations to be retrieved.
table_name: It is used to specify the name of the table to retrieve the records.
columns: It is used to specify the list of columns to be grouped.
Example: Items table:
| ID | NAME | QUANTITY |
| 1 | Electronics | 30 |
| 2 | Sports | 45 |
| 3 | Fashion | 50 |
| 4 | Grocery | 30 |
| 5 | Toys | 50 |
Query:
SELECT quantity FROM items GROUP BY quantity;
Output:
QUANTITY 30 45 50
Explanation:
The ‘items’ is an already existing table from which we are retrieving the unique values of the ‘quantity’.
Syntax: MySQL GROUP BY with WHERE clause.
SELECT expressions FROM table_name WHERE conditions GROUP BY columns;
Parameters: conditions: It is used to specify the conditions to be strictly followed for selection.
Example: Items table:
| ID | NAME | QUANTITY |
| 1 | Electronics | 30 |
| 2 | Sports | 45 |
| 3 | Fashion | 50 |
| 4 | Grocery | 30 |
| 5 | Toys | 50 |
Query:
SELECT quantity FROM items WHERE quantity > 30 GROUP BY quantity;
Output:
QUANTITY 45 50
Explanation:
The ‘items’ is an already existing table from which we are retrieving the unique values of the ‘quantity’ which is greater than 30.
Syntax: MySQL GROUP BY with an aggregate function.
SELECT expressions, aggregate_function (aggregate_expression) FROM table_name WHERE conditions GROUP BY columns;
Parameters:
Aggregate_function: It is used to specify the aggregate function.
Aggregate_expression: It is used to specify the column or expression to be utilized by the aggregate function.
Example 1: MySQL GROUP BY with COUNT function. Items table:
| ID | NAME | QUANTITY |
| 1 | Electronics | 30 |
| 2 | Sports | 45 |
| 3 | Fashion | 50 |
| 4 | Grocery | 30 |
| 5 | Toys | 50 |
Query:
SELECT quantity, COUNT(*) AS “Number of Items” FROM items GROUP BY quantity;
Output:
| QUANTITY | Number of Items |
| 30 | 2 |
| 45 | 1 |
| 50 | 2 |
Explanation:
The ‘items’ is an already existing table from which we are counting the number of Items of the same quantity value.
Example 2: MySQL GROUP BY with SUM function. Items table:
| ID | NAME | QUANTITY |
| 1 | Electronics | 30 |
| 2 | Sports | 45 |
| 3 | Fashion | 100 |
| 4 | Electronics | 90 |
| 5 | Sports | 50 |
Query:
SELECT name, SUM(quantity) AS “Quantity” FROM items GROUP BY name;
Output:
| NAME | QUANTITY |
| Electronics | 120 |
| Sports | 95 |
| Fashion | 100 |
Explanation:
The ‘items’ is an already existing table from which we are calculating the sum of quantities for each unique item name.