MySQL Group By

The MySQL GROUP BY Clause returns aggregated data (value) by grouping one or more columns. It first groups the columns and then applies the aggregated functions to the remaining columns. To display the high-level or aggregated information, you have to use this 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 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.

For this MySQL Group By clause to summarize the row 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 section.

SELECT Occupation,       COUNT(EmpID) FROM customer GROUP BY Occupation;

MySQL Group By Sum Example

In this 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 customer GROUP BY Occupation;

MySQL Group By Distinct Sum Example

This time, we are using the Distinct keyword along with the Sum Function. The following Distinct query finds the Sum of distinct income and the sum of distinct sales in each occupation category.

SELECT Occupation,       SUM(DISTINCT Income),       SUM(DISTINCT Sales) FROM customer GROUP BY Occupation;

Group By Avg Example

Here, we used along with the AVG function to find the Income average and Sales Average in each Occupation.

SELECT Occupation,       AVG(Income),       AVG(Sales) FROM customer GROUP BY Occupation;

MySQL Group By Min and Max Functions

In this example, we used the Min function to find the Minimum Income and Minimum Sales in each Occupation category.

SELECT Occupation,       MIN(Income),       MIN(Sales) FROM customer GROUP BY Occupation; 

Here, we used the Max function to find the Maximum Income and Maximum Sales in each Occupation section.

SELECT Occupation,       MAX(Income),       MAX(Sales) FROM customer GROUP BY Occupation; 

STD, STDDEV, STDDEV_POP, and STDDEV_SAMP functions

In this 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 customer GROUP BY Occupation;

Group By Variance, VAR_POP, and VAR_SAMP Example

Here, we used this one with Variance, VAR_POP, and VAR_SAMP functions to calculate the Variance in each Occupational set.

SELECT Occupation,       VARIANCE(Income),       VAR_POP(Income),       VAR_SAMP(Income) FROM customer GROUP BY Occupation;

Group By Multiple Columns

In this example, we are going to use Multiple Columns. First, it clubs the rows by each Occupation and then Qualification. Next, this query finds the Sum of Income and Total sales in each set.

SELECT Occupation,       Qualification,       SUM(Income),       SUM(Sales) FROM customer GROUP BY Occupation, Qualification;

From the above, though, we categorize the result by the Occupation, and column values are repeating. It is because we used Occupation and Qualification columns.

MySQL Group By Where Clause

Along with this, you can also use the Where Clause to restrict the records selected by the query. And we can also use Order By.

SELECT Occupation,       Qualification,       SUM(Income),       SUM(Sales) FROM customer WHERE Occupation <> 'Clerical' GROUP BY Occupation, Qualification;

Group By Order By Example

We can also use Group By along with Order By. Here, the result set is sorted by the Total Income of each category in descending order.

SELECT Occupation,       Qualification,       SUM(Income),       SUM(Sales) FROM customer GROUP BY Occupation, Qualification ORDER BY SUM(Income) DESC;

Group By Aggregate Functions

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

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.