GROUP BY in MySQL

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.