5 Examples of GROUP BY Clause in SQL with Combination Capabilities

There isn’t a doubt that SQL is an important ability and each programmer, developer, DevOps, and Enterprise analyst ought to know SQL. If you wish to be taught SQL from scratch then you have got come to the correct place. Earlier, I’ve shared many SQL interview questions and the finest SQL programs for newbies, and at the moment, I’m going to share some GROPU By examples in SQL to put in writing aggregation queries. THE GROUP BY clause in SQL is one other essential command to grasp for any programmer. We frequently use the GROUP BY command together with a choose clause for reporting functions, because the GROUP BY clause is especially used to group associated knowledge collectively it is one of the essential SQL instructions for reporting functions. 

Many queries that require using combination perform like sum(), avg(), or rely() requires the grouping of information utilizing the GROUP BY clause. SQL queries which contain GROUP BY and HAVING clauses are additionally a bit complicated for a lot of programmers who haven’t got hands-on expertise in SQL and are sometimes used as SQL interview questions to filter.

On this article, we’ll see some examples of the GROUP BY clause in SQL which assist you to know the place to make use of group by clause and find out how to use GROUP BY together with the SELECT clause. Additionally, you will be taught some SQL guidelines associated to the GROUP BY clause which is out there in some databases significantly in MySQL as a bunch by extensions.

5 SQL GROUP BY and HAVING Examples for Newcomers

Now it is time to see the GROUP BY clause in motion. The next are some examples of how you need to use GROUP BY to combination knowledge after which apply filtering on aggregated or grouped knowledge through the use of the HAVING clause.

1. Group By clause Instance 1 – Discovering duplicate

One of many in style use of the GROUP BY clause is discovering duplicate data within the database. Following SQL question will record staff that are duplicate by way of wage

mysql> choose emp_name, rely(emp_name) 
       from worker group by emp_name having rely(emp_name)>1;
+----------+-----------------+
| emp_name | rely(emp_name) |
+----------+-----------------+
| James    |               2 |
+----------+-----------------+
1 row in set (0.00 sec)

This was a moderately easy instance of discovering duplicate data within the database. If it is advisable to resolve whether or not an worker is duplicate or not based mostly upon a couple of area then it is essential to incorporate all these within the group by clause, in any other case, you’ll get an incorrect rely. You possibly can additional see my article about find out how to discover duplicate data within the database for extra particulars.

2. Group By clause Instance 2 – Calculating Sum

One other in style instance of the group by clause is utilizing an combination perform like sum() and avg().  If you understand, the GROUP BY clause in SQL lets you carry out queries like discovering how a lot every division is paying to staff i.e. whole salaries per division. 

To be able to write an SQL question to search out the overall wage per division, we have to group by the division and use sum(wage) within the choose record as proven within the following SQL question :

mysql> choose dept_id, sum(wage) as total_salary from worker group by dept_id;
+---------+--------------+
| dept_id | total_salary |
+---------+--------------+
|       1 |         3200 |
|       2 |         2850 |
|       3 |         2200 |
|       4 |         2250 |
+---------+--------------+
4 rows in set (0.00 sec)

Then you may additional filter data through the use of having clauses to carry out queries like discovering all departments whose whole wage expenditure is greater than 3000. Right here is an SQL question for that :

mysql> choose dept_id, sum(wage) as total_salary
       from worker group by dept_id having sum(wage) > 3000;
+---------+--------------+
| dept_id | total_salary |
+---------+--------------+
|       1 |         3200 |
+---------+--------------+
1 row in set (0.01 sec)

That is your costliest division by way of wage. It is stood to know the helpful combination perform like rely and sum, if you’re not acquainted with them then I recommend you be a part of an Introductory Information to SQL course on Educative, an interactive studying platform that lets you follow SQL within the browser. 

3. Easy methods to calculate common utilizing group by clause

Just like the earlier group by clause instance, as a substitute of sum() we are able to additionally use avg() to carry out queries like discovering out common wage of staff per division. As soon as once more we have to group by the division and this time as a substitute of sum() combination perform we have to use the avg() perform as proven within the under question :

mysql> choose dept_id, avg(wage) as average_salary from worker group by dept_id;
+---------+----------------+
| dept_id | average_salary |
+---------+----------------+
|       1 |      1066.6667 |
|       2 |      1425.0000 |
|       3 |      1100.0000 |
|       4 |       750.0000 |
+---------+----------------+
4 rows in set (0.00 sec)

Equally, you need to use having clause to additional filter down this outcome set like discovering a division whose common wage is under 1000. Right here is an SQL question for that :

mysql> choose dept_id, avg(wage) as average_salary 
       from worker group by dept_id having avg(wage) < 1000;
+---------+----------------+
| dept_id | average_salary |
+---------+----------------+
|       4 |       750.0000 |
+---------+----------------+
1 row in set (0.00 sec)

That is your most poorly paid division. There isn’t a level going into that division and exploring additional. 

4. Group By instance 4 – Counting data

Just like combination perform sum() and avg(), one other type of combination question could be very in style like counting data. One instance of this type of group by the question is find out how to discover the variety of staff per division?

On this case, we have to group by the division and wish to make use of the rely() combination perform for counting staff as proven within the under SQL question :

mysql>  choose dept_id, rely(emp_id) as number_of_employees 
         from worker group by dept_id;
+---------+---------------------+
| dept_id | number_of_employees |
+---------+---------------------+
|       1 |                   3 |
|       2 |                   2 |
|       3 |                   2 |
|       4 |                   3 |
+---------+---------------------+
4 rows in set (0.00 sec)

Whereas utilizing the rely() perform in SQL, it is value noting the distinction between rely() and rely(area) which may give totally different counts based mostly upon which column on which you’re counting and whether or not that column incorporates NULL or not. 

5. Easy methods to use Group By clause with a couple of column

In lots of sensible circumstances, we use a bunch by clause with a couple of column. Whereas utilizing two or three-column within the group by clause order is essential. The column which comes first on the group by clause might be grouped first after which the second column might be used to do grouping on that group. For instance within the following SQL question outcome set is first grouped by dept_id after which every group is once more grouped by emp_id.

mysql> choose dept_id, emp_id from worker group by dept_id, emp_id;
+---------+--------+
| dept_id | emp_id |
+---------+--------+
|       1 |    101 |
|       1 |    102 |
|       1 |    110 |
|       2 |    103 |
|       2 |    104 |
|       3 |    105 |
|       3 |    108 |
|       4 |    106 |
|       4 |    107 |
|       4 |    109 |
+---------+--------+
10 rows in set (0.00 sec)

One other instance of utilizing a number of columns in group by clause is discovering duplicate data within the desk, the place it’s essential to use all columns that are required to be the identical for a document to be referred to as duplicates. If you wish to follow extra of such superior SQL ideas then I extremely suggest the Full SQL + Databases Bootcamp: Zero to Mastery course by Andrei Negaoie on ZTM Academy.  It is one of the complete and up-to-date programs to be taught SQL. 

Vital factors about Group By clause in SQL

Now it is time to revise and revisit among the essential issues concerning the GROUP BY clause in SQL statements.

1. You cannot use a non-aggregated column title within the choose record if it’s not utilized in Group By clause. For instance following SQL question is against the law and won’t run as a result of it has used a non-aggregate column emp_id within the choose record which isn’t named in Group By clause, on this SQL question solely dept_id is utilized in group by clause.

mysql> choose emp_id, dept_id, max(wage) 
       from worker 
       group by dept_id;

ERROR 1055 (42000): 'check.worker.emp_id' is not in GROUP BY

however MySQL database permits it by its group by extension performance which is disabled within the above situation by enabling ONLY_FULL_GROUP_BY SQL mode.

2. In normal SQL you can’t use a non-aggregated column title within the Having clause which isn’t utilized in group by clause, MySQL database additionally permits it just like the earlier group by extension. Following SQL question is invalid as a result of we’re utilizing max_salary in having a clause that isn’t utilized in group by clause.

mysql> choose  dept_id, max(wage) as max_salary 
       from worker 
       group by dept_id having max_salary > 1000;

ERROR 1463 (42000): Non-grouping area 'max_salary' is used in HAVING clause

3. One other rule of utilizing group by clause in normal SQL is that you just can’t use an alias within the HAVING clause, the earlier SQL question can be an instance of this group by rule. That is additionally allowed within the MySQL database. 

You possibly can disable all MySQL group by extension options through the use of ONLY_FULL_GROUP_BY SQL mode in MySQL. To be able to change SQL mode from the MySQL command line you need to use the next MySQL instructions :

mysql> SET GLOBAL sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,
           NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY";
Question OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.sql_mode;
+-----------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                 |
+-----------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

You can too set MySQL SQL mode for a single shopper session through the use of the phrase SESSION as a substitute of GLOBAL as proven under :

mysql> SET SESSION sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,
             NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY";
Question OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.sql_mode;
+-----------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                 |
+-----------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Additionally simply altering MySQL SQL mode to World won’t take impact within the present session till you restart a brand new session. To be able to allow ONLY_FULL_GROUP_BY within the present MySQL session utilizing the above question.

That is all on the GROUP BY clause instance in SQL queries. Now we have seen the place to make use of the GROUP BY command and Easy methods to use the GROUP BY clause to group knowledge in SQL. As I stated GROUP BY and HAVING clause is among the must-know for any programmers because it’s fairly frequent within the SQL world and significantly essential for reporting functions.

Different SQL and Database Articles you might like

• Prime 5 Web sites to be taught SQL for FREE (web sites)
• My favourite programs to be taught SQL and Database (programs)
• Prime 5 Programs to be taught PostgreSQL for Newcomers (programs)
• 10 SQL queries from Interviews (queries)
• Prime 5 Programs to be taught Microsoft SQL Server (Programs)
• 5 Free Programs to be taught Database and SQL (programs)
• 5 Books to Be taught SQL Higher (books)
• Easy methods to be a part of greater than two tables in a single question (article)
• 10 Free Programs to be taught Oracle and SQL Server (programs)
• Prime 5 Programs to be taught MySQL for Newcomers (programs)
• Distinction between WHERE and HAVING clause (reply)
• Prime 5 SQL books for Superior Programmers (books)
• Distinction between SQL, T-SQL, and PL/SQL? (reply)
• 5 Free Programs to Be taught MySQL database (programs)
• Prime 5 On-line Programs to Be taught SQL and Database (programs)

Thanks for studying this text, if you happen to discover this SQL GROUP By examples helpful then please share them with your mates and colleagues. When you have any questions or suggestions then please drop a word. 

P. S. – If you’re new to the SQL world and on the lookout for a free SQL and database course to start out studying fundamentals then I recommend you undergo Introduction to Databases and SQL Querying course on Udemy. It is fully free, all you have got is to create is a Udemy account and you’ll entry the entire course.