Tutorial Gateway

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.

TIP: 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 regions. 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 which are not part of an Aggregate Function have to place after this Group by.

In this article, we will show you, How to use Group By clause to group the data. For this Group By 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 query counts the employee ids in each occupation group.

SELECT Occupation,
       COUNT(EmpID)
 FROM `MySQL Tutorial`.customer
 GROUP BY Occupation;

OUTPUT

MySQL GROUP BY Clause 2

MySQL Group By Sum Example

In this example, we use Sum function to calculate the total income and total sales in each Occupation.

SELECT Occupation,
       SUM(Income),
       SUM(Sales)
 FROM `MySQL Tutorial`.customer
 GROUP BY Occupation;

OUTPUT

MySQL GROUP BY Sum 3

MySQL Group By Distinct Sum Example

This time, we are using the Distinct keyword along with the Sum Function. The following 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;

OUTPUT

MySQL GROUP BY Sum Distinct 4

MySQL Group By Avg Example

Here, we used 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;

OUTPUT

MySQL Group By AVG

MySQL Group By Min Example

In this 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;

OUTPUT

MySQL GROUP BY Min 6

MySQL Group By Max Example

Here, we used 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;

OUTPUT

MySQL GROUP BY Max 7

Group By STD Example

In this example, we are going to use STD, STDDEV, STDDEV_POP and STDDEV_SAMP functions 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;

OUTPUT

MySQL GROUP BY Std 8

Group By Variance Example

Here, we used the 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;

OUTPUT

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, it will find 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;

OUTPUT

MySQL GROUP BY Multiple Columns 10

From the above, though, we grouped by the Occupation, column values are repeating. This 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;

OUTPUT

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 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;

OUTPUT

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;

OUTPUT

MySQL GROUP BY Clause 13

Thank You for Visiting Our Blog