MySQL GROUP BY Clause 13

MySQL Group 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.

For this MySQL Group By clause to summary the row data demonstration, We are going to use the below-shown data.

Customer Table Rows 1

MySQL Group By Count Single Column

In this example, we used a single column in the MySQL 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 Single Column 2

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 Sum 3

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 sum of distinct sales in each occupation category.

SELECT Occupation,
       SUM(DISTINCT Income),
       SUM(DISTINCT Sales)
 FROM customer
 GROUP BY Occupation;
MySQL GROUP BY Sum Distinct 4

Avg Example

Here, we used the Group By 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 AVG

MySQL Group By Min and Max Examples

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;
Min and Max Functions 6

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

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

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

In this example, we are going to use Multiple Columns. First, it club 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;
MySQL GROUP BY Multiple Columns 10

From the above, though, we categorize the result by the Occupation, 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;
MySQL GROUP BY Where 11

Order By Example

We can also use MySQL Group By along with the 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;
MySQL GROUP BY Order By 12

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