GROUP BY In SQL

Introduction

In this write-up, we will learn about one of the essential SQL commands GROUP BY.

Whenever we start to learn something new, the inner curiosity asks about its utility, so we will also look for the utility of the command GROUP BY, which the syntax of writing it will follow along with some examples.

If you have prior knowledge of other SQL commands like SELECT, COUNT, ORDER BY, then learning the GROUP BY command would be like a cakewalk for you.

Source-http://wikipedia.org/

So Ninjas!  Let us learn the GROUP BY clause and how to solve SQL queries.

What is the GROUP BY clause?

As the name suggests, the GROUP BY clause is a SQL clause that groups the identical data into the desired groups. There are many scenarios in day-to-day life we require to group the data to count the occurrence of the information, and we can do this with the help of the GROUP BY clause.

Let us understand this more deeply with the help of the following figure.

Source- http://brafton.com/

In the above figure, we can see people with red, yellow, and green colors standing in a randomly arranged manner. If we want to count them, it will be challenging for us to do, but if we group them likewise in the figure after the arrow, The job of counting them will get easier and will be less time-consuming.

Utility and Importance

Suppose The chancellor of your college wants to distribute a scholarship of Rs 10000 to the students of the CSE branch and a scholarship of Rs 5000 to the students of the ECE branch. For this, the management wants to know the count of CSE and ECE students. We would thus require to group the number of students studying in the university based on their stream and subsequently count them. The GROUP BY clause will help us group them. There could be numerous occasions of the requirement of grouping the data to be more readable and accessible to us.

With the help of the above examples, it is evident how functional the GROUP BY clause is, as we can not manually group massive data.

The GROUP BY clause becomes more valuable when we use this with the SQL aggregate functions like COUNT(), AVG(), MAX(), MIN(), etc.

Aggregate functions

As we discussed, the GROUP BY clause becomes more valuable when used with aggregate functions. This section will look at various aggregate functions offered by SQL, their operations, and how we use them.

COUNT()

This aggregate function counts all the data entries of the desired row or column. This is used in the scenario where we need to count the number of occurrences of a particular data.

AVG()

This aggregate function takes the average of the data entries of the desired row or column.

We often need to calculate the average of entries, so this function is beneficial.

MIN()

This aggregate function traverses throughout the data and returns the minimum of them.

Yes, Ninjas! You read that right “Don’t be a minimum guy,” But in the real world, we often come across situations where we want to know the minimum.

MAX()

This aggregate function traverses throughout the data and returns the maximum of them.

If you want to find out the topper of your stream, you can use this function, and it will return you the desired result.

SUM()

This aggregate function returns the cumulative of all values. We often use this function to calculate the total expenses on particular things.

Now Since we have learned how vital the GROUP BY clause is, we will move forward and look at its syntactic structure because, in SQL, we make so many errors while writing the query as we mix it with our English language.

Syntax

Let us look at the basic syntax of using the GROUP BY clause.

Note:- The use of the WHERE command is optional. You may only use that when you need to group the row that abides by certain conditions.

We often use the GROUP BY clause with aggregate functions or the ORDER BY clause, so we look at the syntax when an aggregate function is involved, and the ORDER BY clause is applied.

Note:- While writing the column name along with the GROUP BY clause, we must use the same column name with the SELECT command.

Now let us go through some examples that will help you better understand the GROUP BY clause-related queries’ writing format.

Important note- Although there is an ISO standard for SQL, most of the implementation slightly vary in syntax. So we may encounter queries that work in SQL servers but do not work in MySQL.

Examples

Let us look at the following table.

Suppose the company wants to count the number of employees working in different departments and thus send them the goodies according to their department.

To count this, they will require arranging the above table in a grouped form with the help of the GROUP BY clause, and then they will also use the aggregate function COUNT() to count the number of employees in different departments.

Let us see what query they will need to execute to get the desired results.

Output

Q:- Can we use two or more aggregate functions in the same query, according to our requirements.?

Ans:-Yes, we can use as many aggregate functions as we require. For example, see the following query, where we calculate the count and sum of the data.

Q:- How can we Sort the groups formed in ascending or descending order after using the GROUP BY clause.?

Ans:- We can sort the data by using another SQL clause, i.e., ORDER BY. We need to mention the order ASC/DESC for ascending and descending, respectively.

Q:-  Can we rename the column formed by the aggregate function?

Ans:- Suppose we want to rename the COUNT(DEPARTMENT) table as FREQUENCY, then we need to execute the following query.

Key takeaways

This article has discussed the vital GROUP BY clause in SQL. With the help of examples, we learned about its utility and importance. The GROUP BY clause is instrumental in handling vast amounts of data, as manually arranging them into desired groups is more time-consuming. We also learned about the aggregate functions often used with the GROUP BY clause.

Then we discussed the syntactical structure of the GROUP BY clause and implemented it by seeing some real-world examples. And at last, we saw some critical questions frequently asked in interviews.

I hope you understood the GROUP BY clause clearly and are ready to implement it in your table but don’t stop now as we got you covered with CodeStudio the one-stop platform for practicing problems, and top 100 interview questions on SQL.