MySQL GROUP BY Clause with Examples

MySQL GROUP BY and HAVING Clause with Examples

MySQL GROUP BY Clause with Examples
MySQL GROUP BY Clause with Examples

Summary :

In this Tutorial, you will learn how to use MySQL GROUP BY clause.

What is MySQL GROUP BY Clause ?

  • The GROUP BY clause combines all those records that have identical values in a particular field or a group of fields. In other words, The GROUP BY clause is a SQL command that is used to group rows that have the same values.
  • The GROUP BY is used in SELECT statements to divide the table into groups.
  • Grouping can be done by a column name, or with aggregate functions in which case the aggregate produces a value for each group.
  • Optionally it is used in conjunction with Aggregate Functions to produce summary reports from the database.

The SQL queries that contain GROUP BY clause are called grouped queries and return a single roe for every grouped item.

MySQL GROUP BY Syntax

Now we know about the GROUP BY clause, let’s look at the syntax for a basic GROUP BY query.

Select Statements...
FROM [Table Name]
GROUP BY column_name1[, column_name2,...]
Having [condition]

Here

  • “SELECT statements…” is the standard SQL SELECT command query.
  • “FROM [Table Name]” in which table you want to perform GROUP BY Commands.
  • “[, column_name2,…]” is optional; represents other column names when grouping is done on more than one column.
  • “Having [condition]” is optional; it is used to restrict the rows affected by GROUP BY clause. It is similar to the WHERE clause.

1. GROUP BY command in Single column

In order to understand the effect of MySQL GROUP BY clause, let’s execute a simple query that returns all the gender entries from the employee table.

employee_numberfull_namesgenderdate_of_birthphysical_addresspostal_addresscontct_numberemail
1Mohit MeenaMale21-07-1980First Street Plot No 4Private Bag0759 253 542[email protected]
2Gloria WilliamsFemale23-06-1980Melrose 123NULLNULL[email protected]
3Anushka SenFemale24-08-1981
3rd Street 34
NULL
12345
[email protected]
4
Howard Wolowitz
Male14-02-19842nd Street 23NULLNULLNULL
5Leslie WinkleMaleNULLWoodcrestNULL845738767NULL
6
Rajesh Koothrappali
MaleNULL3rd Street 34NULL976736763NULL
7Sheldon CooperMaleNULL2nd Street 24NULL
987636553
NULL
8Janet Smith JonesFemale24-08-1981Woodcrest
P.O. Box 4563

987636554
NULL
Table : employee
Query
SELECT gender FROM employee
Output
gender
Male
Female
Female
Male
Male
Male
Male
Female

If we want to get the unique values for gender. We can use a following query –

Query
SELECT gender FROM employee
GROUP BY gender
Output
gender
Male
Female

Note :

Only two results have been returned. This is because we have only two gender types Male and Female.
The GROUP BY clause group all the “Male” member together and group all the “Female” member together.
As you can see, the GROUP BY return unique occurrences of gender values. It works like the DISTINCT operator as shown below.

Query
SELECT DISTINCT gender
FROM employee
Output
gender
Male
Female

2. GROUP BY commands in multiple columns

Suppose that we want to get a list of gender and date of birth.

Query
SELECT gender,date_of_birth 
FROM employee  ;
Output
genderdate_of_birth
Male21-07-1980
Female23-06-1980
Female24-08-1981
Male14-02-1984
MaleNULL
MaleNULL
MaleNULL
Female24-08-1981

The above result has many duplicates.

Let’s execute the same query using group by in SQL –

Query
SELECT gender,date_of_birth 
FROM employee
GROUP BY gender,date_of_birth  ;
Output
genderdate_of_birth
Male21-07-1980
Female23-06-1980
Female24-08-1981
Male14-02-1984
MaleNULL

The GROUP BY clause operates on both the gender and date_of_birth to identify unique rows in our above example.

If the gender is the same but the date of birth is different, then a row is treated as a unique one .If the gender and the date of birth is the same for more than one row, then it’s considered a duplicate and only one row is shown.

3. GROUP BY and aggregate functions

Suppose we want total number of males and females in our database. We can use the following script shown below to do that.

Query
SELECT gender,COUNT(employee_number)  
FROM employee 
GROUP BY `gender`;
Output
genderdate_of_birth
Male5
Female3

The results shown below are grouped by every unique gender value posted and the number of grouped rows is counted using the COUNT aggregate function.

4. HAVING clause in MySQL

The HAVING clause places conditions on groups in contrast to WHERE clause that places conditions on individual rows. While WHERE conditions cannot include aggregate functions, HAVING conditions can do so.

It’s not always that we will want to perform groupings on all the data in a given table. There will be times when we will want to restrict our results to a certain given criteria. In such cases , we can use the HAVING clause

Suppose that we want to get a list of employees group by gender and date of birth of address Woodcrest. We should use the following query to perform this :

Query
SELECT * FROM employee 
GROUP BY gender,date_of_birth 
HAVING physical_address = Woodcrest;
Output
employee_numberfull_namesgenderdate_of_birthphysical_addresspostal_addresscontct_numberemail
5Leslie WinkleMaleNULLWoodcrestNULL845738767NULL
8Janet Smith JonesFemale24-08-1981Woodcrest
P.O. Box 4563

987636554
NULL
Table : employee

5. Nested Groups – Grouping on Multiple Columns

With GROUP BY clause, you can create groups within groups. Such types of grouping is called Nested grouping. This can be done by specifying in GROUP BY expression, where the first field determines the highest group level, the second field determines the second group level, and so on.
In order to fully understand this concept, consider Table emp1.

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
8369SMITHCLERK89021990-12-18800.00NULL20
8499ANYASALESMAN86981991-02-201600.00300.0030
8521SETHSALESMAN86981991-02-221250.00500.0030
8566MAHADEVANMANAGER88391991-04-022985.00NULL20
8654MOMINSALESMAN86981991-09-281250.001400.0030
8698BINAMANAGER88391991-05-012850.00NULL30
8839AMIRPRESIDENTNULL1991-11-185000.00NULL10
8844KULDEEPSALESMAN86981991-09-081500.000.0030
8882SHIAVNSHMANAGER88391991-06-092450.00NULL10
8886ANOOPCLERK88881993-01-121100.00NULL20
8888SCOTTANALYST85661992-12-093000.00NULL20
8900JATINCLERK86981991-12-03950.00NULL30
8902FAKIRANALYST85661991-12-031300.00NULL20
8934MITACLERK88821992-01-231300.00NULL10
database table emp1


See there are multiple records having same value for field Deptno, we can group records on the basis of field Deptno. For instance, if you want to count the number of employees in each group, you need to issue a query statement as given below :


Query
SELECT COUNT(empno) FROM emp1
GROUP BY Deptno ;
Output
count (empno)
3
5
6

But can you make out, these are employee-counts for which departments ? To get this information, you may modify the SELECT list as :


Query
SELECT Deptno, COUNT(empno)
FROM emp1
GROUP BY Deptno ;
Output
deptnocount (empno)
103
205
306

See, now it is more clear. But one thing that you should keep in mind is that while grouping, you should include only those values in the select list that either have the same value for a group or contain a group (aggregate) function i.e., a group-expression. Like in the above query, the first expression Deptno field has one (same) value for a group and othe expression COUNT(empno) contains a group function.


MySQL as such would not create any error even if you include a non-group expression in the select-list. A non-group field (or expression) is the field that has different values in the roes belonging to the group.
In case, it will return the value from first record of the group for that non-group field e.g., if you command like :

Query
SELECT deptno, count(empno), mgr
From emp1
GROUP BY Deptno ;
Output
deptnocount (empno)mgr
103NULL
2058902
3068698

To create a group within a group i.e., nested group, you need to specify multiple fields in the GROUP BY expression. if you have look at the records emp1 table, you make out that there exists a group of jobs within the department group as there are same values for job field, in one department group’s records.
To group records job wise within Deptno wise, you need to issue a query statement like :

Query
SELECT Deptno, Job, COUNT(empno)
FROM emp1
GROUP BY Deptno, Job ;
Output
deptnojobcount (empno)
10CLERK1
10MANAGER1
10PRESIDENT1
20ANALYST2
20CLERK2
20MANAGER1
30CLERK1
30MANAGER1
30SALESMAN4

6. Non-Group Expression with GROUP BY


As mentioned before, if you include a non-group expression in the select-list of a query with GROUP BY, MySQL will not produce any error. Rather it will pick value of the specified non-group field from the first row of the group. But we do not recommend this practice because it will produce ambiguous results. For instance, consider the following query and its output.

Query
SELECT ename, sum(sal)
FROM emp1
GROUP BY Deptno ;
Output
enamesum (sal)
AMIR8750.00
SMITH10885.00
ANYA9400.00

See, isn’t conveying that AMIR’s salary-sum is 8750.00, SMITH’s 10885.00 and ANYA’s 9400.00 ?
Thus, we recommend not to use non-group expressions in GROUP BY query unless otherwise necessary.

Summary

  • The GROUP BY Clause SQL is used to group rows with same values.
  • The GROUP BY Clause is used together with the SQL SELECT statement.
  • The SELECT statement used in the GROUP BY clause can only be used contain column names, aggregate functions, constants and expressions.
  • SQL Having Clause is used to restrict the results returned by the GROUP BY clause.
  • MySQL GROUP BY Clause is used to collect data from multiple records and returned record set by one or more columns.

MySQL GROUP BY learn by video

video credit : Steve Griffith

Read More :

MySQL Function with Examples

Frequently Asked Questions

Q 1. What is Group By clause with example?

Ans. The GROUP BY clause is a SQL command that is used to group rows that have the same values. The GROUP BY clause is used in the SELECT statement. Optionally it is used in conjunction with aggregate functions to produce summary reports from the database.

Q 2. What is use of Group By clause in mysql?

Ans. The MYSQL GROUP BY Clause is used to collect data from multiple records and group the result by one or more column. It is generally used in a SELECT statement. You can also use some aggregate functions like COUNT, SUM, MIN, MAX, AVG etc.

Q 3. Can we use group by and having clause together?

Ans. HAVING Clause always utilized in combination with GROUP BY ClauseHAVING Clause restricts the data on the group records rather than individual records.

Q 4. What is group by in SQL with example?

Ans. The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”. The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.

Q 5. Why do we use group by clause?

Ans. Group by clause is used to group the results of a SELECT query based on one or more columns. It is also used with SQL functions to group the result from one or more tables. Syntax for using Group by in a statement.

Q 6. What is the use of order by clause?

Ans. The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

Q 7. Can I use where with group by?

Ans. A query can contain both a WHERE clause and a HAVING clause. The HAVING clause is then applied to the rows in the result set. Only the groups that meet the HAVING conditions appear in the query output. You can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function.

Q 8. How do you use group by and order by together?

Ans. To summarize, the key difference between order by and group by is:

  1. ORDER BY is used to sort a result by a list of columns or expressions.
  2. GROUP BY is used to create unique combinations of a list of columns that can be used to form summaries.

Q 9. What is the purpose of MySQL GROUP BY command?

Ans. The GROUP BY clause is a SQL command that is used to group rows that have the same values. The GROUP BY clause is used in the SELECT statement. Optionally it is used in conjunction with aggregate functions to produce summary reports from the database. That’s what it does, summarizing data from the database.

Q 10. What is MySQL GROUP BY command in DBMS?

Ans. The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”. The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *