Post
Ask Question

GROUP BY in MySQL

INTRODUCTION

 
In this tutorial, I am going to explain about GROUP BY in MySQL with examples. Without wasting time, let’s start.
 

GROUP BY Clause

 
In MySQL, the GROUP BY statement is for applying an association on the aggregate functions for a  group of the result-set with one or more columns. Group BY is very useful for fetching information about a group of data. If we have only one product of each type, then GROUP BY would not be all that useful.
 
The GROUP BY statement only shows when you have many similar things.
 
The GROUP BY clause is often used with aggregate functions like SUM, AVG, COUNT, MIN, and MAX.
 
Syntax:
 
SELECT temp1, temp2, temp3,...tempn, aggregate function(tempi)
From <table_name>
WHERE conditions
GROUP BY temp1, temp2, temp3,…tempn;
 
GROUP BY clause appears and is used after SELECT, FROM, WHERE clauses and before HAVING, ORDER BY clauses.
 
flowchart
 

MySQL GROUP BY Examples

 
For example
 
If we have a number of products of the same type, and we want to find out some statistical information like the minimum, maximum, or other top-level info, we would use GROUP BY. The column that you GROUP BY must also be in your SELECT statement. Remember to group by the column you want information about and not the one you are applying the aggregate function on.
 
Without wasting time, let’s create a database and a table and insert some rows into it:
 
Create a database
  1. CREATE database GROUP_BY; 
Create a table
  1. USE GROUP_BY;  
  2. CREATE TABLE emp_info  
  3. (  
  4.         emp_id int,  
  5.         emp_name VARCHAR(50),  
  6.         emp_salary BIGINT,  
  7.         emp_status varchar(50)  
  8. ); 
Now insert some rows  into the emp_info table:
  1. INSERT INTO emp_info VALUES(1, 'Onkar', 200000, 'CONFIRMED');  
  2. INSERT INTO emp_info VALUES(2, 'Admin', 300000, 'Probation');  
  3. INSERT INTO emp_info VALUES(3, 'Rohit', 400000, 'CONFIRMED');  
  4. INSERT INTO emp_info VALUES(4, 'John', 100000, 'Notice');  
  5. INSERT INTO emp_info VALUES(5, 'Simran', 50000, 'Notice');  
  6. INSERT INTO emp_info VALUES(6, 'Jasmine', 150000, 'CONFIRMED');  
  7. INSERT INTO emp_info VALUES(7, 'Riya', 180000, 'Probation'); 
Here, I am also providing you the database with various used tables on which I am showing you the various examples.
 

A)    Simple MySQL GROUP BY example  

 
Here, if you want to group by the employee name into subgroups,  then you can use group by clause. Use:
  1. SELECT *  
  2.     FROM emp_info  
  3. GROUP BY emp_status 
7
 

B)    Examples of MySQL GROUP BY with Aggregate Functions

 
Aggregate functions works on a set of rows and returns a single value. GROUP BY is used to perform a calculation on multiple rows and returns a single value.
 
Query1
  1. SELECT emp_name, count(emp_status)  
  2. FROM emp_info  
  3. GROUP BY emp_name 
3
 
It seems that the GROUP BY clause only scans for unique occurrences in the status column and returns the result set.
 
Query2
  1. SELECT emp_name, (emp_salary * 12) as CTC  
  2. FROM emp_info  
  3. GROUP BY emp_name 
4
 

C)    Example of MySQL GROUP BY with expression

 
Expression is also used to group by the rows in a database as an additional filter. Now, let’s see.
  1. SELECT emp_name, (emp_salary * 12) as CTC  
  2. FROM emp_info  
  3. WHERE emp_status = 'Confirmed'  
  4. GROUP BY emp_name 
5 
 

D)    Example of MySQL GROUP BY with HAVING Clause

 
Having clause is used to filter the returned data from the GROUP BY clause. Here, I will show you the HAVING clause to filter the emp_salary > 100000
  1. SELECT emp_id, emp_name, (emp_salary * 12) as CTC  
  2. FROM emp_info  
  3. WHERE emp_status = 'Confirmed' or (emp_status = 'Notice' and emp_name = 'Simran')  
  4. GROUP BY emp_salary  
  5. HAVING emp_salary > 100000 
6
 

E)    Example of MySQL GROUP BY in standard form

 
MySQL also allows us to sort the group order in which the results are returned. The default order is ascending. If I want to see the result of the query above in the descending order, I can do it as follows.
 
Query: The given example shows that emp_salary in desc form.
  1. SELECT emp_id, emp_name, (emp_salary * 12) as CTC  
  2. From emp_info  
  3. WHERE emp_status = 'Confirmed'  
  4. or(emp_status = 'Notice'  
  5.     and emp_name = 'Simran')  
  6. GROUP BY emp_salary  
  7. HAVING emp_salary > 100000  
  8. ORDER BY emp_salary desc 
7
 

CONCLUSION

 
In this article, I have discussed the concept of GROUP BY in MySQL with various examples.
 
I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL.
 
Thanks for reading this article!


Similar Articles