MySQL GROUP BY Clause 13

MySQL Group By

by

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 Clause 1

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 Clause 2

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;
MySQL GROUP BY Sum 3

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 Sum Distinct 4

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 AVG

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;
MySQL GROUP BY Min 6

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;
MySQL GROUP BY Max 7

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;
MySQL GROUP BY Std 8

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 Variance 9

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;
MySQL GROUP BY Multiple Columns 10

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 Where 11

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;
MySQL GROUP BY Order By 12

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;
MySQL GROUP BY Clause 13