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.

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 

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 

Query2

It seems that the GROUP BY clause only scans for unique occurrences in the status column and returns the result set.

1. SELECT

    emp_name, (emp_salary * 12) 

   as

    CTC  

2. FROM

    emp_info  

3. GROUP

    

   BY

    emp_name 

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 

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 

E)    Example of MySQL GROUP BY in
standard form

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

    

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.The given example shows that emp_salary in desc form.

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!