The MySQL GROUP BY Clause returns an aggregated data (value) by grouping one or more columns. It first groups the columns and then applies the aggregated functions on the remaining columns. To display the high-level or aggregated information, you have to use this MySQL Group by clause
For example, to find the Total Sales by continent or region, use MySQL Group By Clause to group the Country names in a Continent or region. Next, use the aggregate SUM function to calculate the total.
MySQL GROUP BY Clause Syntax
The syntax of the MySQL Group By Clause can be written as:
SELECT [Column1],...[ColumnN],
Aggregate_Function(Column_Name)
FROM [Source]
WHERE [Conditions] -- Optional
GROUP BY [Column1],...[ColumnN]
ORDER BY Columns
- Column1…N: Choose the columns from a table(s).
- Aggregate Functions: Use any of the aggregate functions. COUNT, SUM, AVG, AVG, MIN, MAX, STD, and VARIANCE are the functions that we can use.
- Group By: Columns that are not part of an Aggregate Function have to place after this Group by.
For this Group By clause to group the data demonstration, We are going to use the below-shown data.
MySQL Group By Count Single Column
In this example, we used a single column in the Group By Clause, and Count function. The following MySQL query counts the employee ids in each occupation group.
SELECT Occupation,
COUNT(EmpID)
FROM `MySQL Tutorial`.customer
GROUP BY Occupation;
MySQL Group By Sum Example
In this MySQL example, we use the Sum function to calculate the total income and total sales in each Occupation. Please refer to aggregated functions article.
SELECT Occupation,
SUM(Income),
SUM(Sales)
FROM `MySQL Tutorial`.customer
GROUP BY Occupation;
Group By Distinct Sum Example
This time, we are using the Distinct keyword along with the Sum Function. The following Group By Distinct query finds the Sum of distinct income and sum of distinct sales in each occupation group.
SELECT Occupation,
SUM(DISTINCT Income),
SUM(DISTINCT Sales)
FROM `MySQL Tutorial`.customer
GROUP BY Occupation;
MySQL Group By Avg Example
Here, we used the MySQL AVG function to find the Income average and Sales Average in each Occupation.
SELECT Occupation,
AVG(Income),
AVG(Sales)
FROM `MySQL Tutorial`.customer
GROUP BY Occupation;
MySQL Group By Min Example
In this Group By example, we used Min function to find the Minimum Income and Minimum Sales in each Occupation Group.
SELECT Occupation,
MIN(Income),
MIN(Sales)
FROM `MySQL Tutorial`.customer
GROUP BY Occupation;
Group By Max Example
Here, we used the MySQL Max function to find the Maximum Income and Maximum Sales in each Occupation Group.
SELECT Occupation,
MAX(Income),
MAX(Sales)
FROM `MySQL Tutorial`.customer
GROUP BY Occupation;
Group By STD Example
In this MySQL example, we are going to use STD, STDDEV, STDDEV_POP and STDDEV_SAMP functions along with Group By to calculate the Standard Deviation of each Occupation.
SELECT Occupation,
STD(Income),
STDDEV(Income),
STDDEV_POP(Income),
STDDEV_SAMP(Income)
FROM `MySQL Tutorial`.customer
GROUP BY Occupation;
Group By Variance Example
Here, we used the Group By with Variance, VAR_POP, and VAR_SAMP functions to calculate the Variance in each Occupational group.
SELECT Occupation,
VARIANCE(Income),
VAR_POP(Income),
VAR_SAMP(Income)
FROM `MySQL Tutorial`.customer
GROUP BY Occupation;
MySQL Group By Multiple Columns
In this example, we are going to use Multiple Columns. First, it groups the rows by each Occupation and then Qualification. Next, this query finds the Sum of Income and Total sales in each group.
SELECT Occupation,
Qualification,
SUM(Income),
SUM(Sales)
FROM `MySQL Tutorial`.customer
GROUP BY Occupation, Qualification;
From the above, though, we grouped by the Occupation, column values are repeating. It is because we used Occupation and Qualification columns in the group by clause.
MySQL Group By Where Clause
Along with this Group By Clause, you can also use Where Clause to restrict the records selected by the query.
SELECT Occupation,
Qualification,
SUM(Income),
SUM(Sales)
FROM `MySQL Tutorial`.customer
WHERE Occupation <> 'Clerical'
GROUP BY Occupation, Qualification;
MySQL Group By Order By Example
We can also use Order By along with the Group By. Here, the grouped result is sorted by the Total Income of each group in descending order.
SELECT Occupation,
Qualification,
SUM(Income),
SUM(Sales)
FROM `MySQL Tutorial`.customer
GROUP BY Occupation, Qualification
ORDER BY SUM(Income) DESC;
Group By Aggregate Functions Example
In this group by example, We are using all the functions within this command prompt.
SELECT Qualification, Occupation,
SUM(Income),
AVG(Income),
MIN(Income),
MAX(Income),
STD(Income),
VARIANCE(Income)
FROM customer
GROUP BY Qualification, Occupation;
