Read this article to understand the Group By clause along with the examples. We have shared few examples and we have shared basic as well as advance level common interview questions also.
In this post, I will provide you a short overview of Group By clause with all its options. In addition, we have covered some questions which we receive from the audience.
First, let’s talk about the theory of Group By clause and why we should use it.
Definition of Group By Clause
The Group By Clause or Keyword is used to Group the data based on values. This clause combines the data based on values and return the results along with aggregate functions. We can combine one or more aggregate functions on grouped data. When we want to summarise the data based of groups then we use GroupBy Clause.
Reasons to Use Group By Clause
As it’s name suggest we can use the Group By clause to Grouping the data. Below are the some key areas where we need to use this clause.
- Aggregate functions along with Group By allow you to summarise data by performing calculations on groups of rows or an entire table.
- Aggregate functions can be used to combine data from multiple rows into a single value.
- Group By along with aggregate functions are useful for calculating various statistics, such as minimum (MIN) and maximum (MAX) values, average (AVG), median, mode, and standard deviation (STDDEV), among others.
- Group by along with aggregate functions can be used to perform data validation checks on your grouped data.
Syntax
SELECT column1, column2, .., columnN, aggregate_function(column)..
FROM table_name
[WHERE condition]
GROUP BY column1, column2, ..., columnN
[HAVING condition]
[ORDER BY columns];We can use any aggregate functions which is mentioned here along with Group By. Cannot use group by without any aggregate functions. We can use Where clause before Group By clause. If we have to apply any conditions on groupby data then we can use Having clause like Where clause after Group By clause. We can sort the aggregated data after groupby clause.
Example
Example 1:
Lets see the graphical representation of the Group By clause. We have “Customers” table, having 5 records with few columns. Print the number of records country wise.
| customer_id | first_name | last_name | age | country |
|---|---|---|---|---|
| 1 | John | Doe | 31 | USA |
| 2 | Robert | Luna | 22 | USA |
| 3 | David | Robinson | 32 | UK |
| 4 | John | Sinha | 22 | UK |
| 5 | James | Doe | 19 | INDIA |
GROUP BY country; |
| count(*) | country |
|---|---|
| USA | |
| UK | |
| INDIA |
Above example print the number of records per country. Lets sort the above records using ascending order.
Example 2:
| customer_id | first_name | last_name | age | country |
|---|---|---|---|---|
| 1 | John | Doe | 31 | USA |
| 2 | Robert | Luna | 22 | USA |
| 3 | David | Robinson | 32 | UK |
| 4 | John | Sinha | 22 | UK |
| 5 | James | Doe | 19 | INDIA |
GROUP BY country Order By country ASC; |
| count(*) | country |
|---|---|
| INDIA | |
| UK | |
| USA |
Lets show only those country which has more than one records.
Example 3:
| customer_id | first_name | last_name | age | country |
|---|---|---|---|---|
| 1 | John | Doe | 31 | USA |
| 2 | Robert | Luna | 22 | USA |
| 3 | David | Robinson | 32 | UK |
| 4 | John | Sinha | 22 | UK |
| 5 | James | Doe | 19 | INDIA |
FROM Customers GROUP BY country HAVING count(*) > 1 Order By country ASC; |
| count(*) | country |
|---|---|
| UK | |
| USA |
I hope all above example clears the concept of Group By clause and it’s use.
Linking
Group By clause is used in Select statement to show grouped data together. We can use Where before Group By. We can you all aggregate functions with Group By. Please refer all the links for understanding the Group By in depth.
SQL Group By Clause with Latest SQL Interview Questions 2023
Basic Group By SQL Interview questions
- What is the purpose of the GROUP BY clause in SQL?
- What are some common aggregate functions used with the GROUP BY clause?
- How do you use the GROUP BY clause in a SQL query?
- Can you use WHERE and HAVING clauses with the GROUP BY clause in SQL?
Intermediate Group By SQL Interview questions
- What is the order of execution for clauses in a SQL query that includes GROUP BY?
- How can you filter the result set of a GROUP BY query based on the aggregated values?
Advanced Group By SQL Interview questions
- Can you use multiple columns in the GROUP BY clause to create nested groups in SQL?
Questions covered from comment section
In this section we will cover the new questions as well as answers. Please comment the questions into comment section so we will include it here. Below are few questions which we thought should be covered.
Que : What is the order of execution for clauses in a SQL query that includes GROUP BY?
Ans : Already we have covered this question in order by clause. still I am proving the high level sequence of execution of groupby clause.
- FROM: Specifies the table(s) from which to retrieve data.
- WHERE: Filters rows based on specified conditions.
- GROUP BY: Groups rows based on specified column(s).
- HAVING: Filters groups based on specified conditions.
- SELECT: Retrieves the columns and applies aggregate functions as needed.
- ORDER BY: Sorts the result set based on specified column(s).
- LIMIT/OFFSET: Specifies the subset of rows to retrieve (if applicable).
Quiz
Click on this link to solve few MCQ questions on Group By clause.
I hope you enjoyed this session. Happy offline learning… Lets meet in next session.
