SQL GROUP BY

Introduction

This article details the SQL group by clause, when to use it, and what to consider when using it.

What is SQL GROUP BY?

The SQL GROUP BY clause is will use in combined with the SELECT statement to organise identical data. This GROUP BY phrase follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.

The SQL GROUP BY Statement is used to organise identical data into groups using some functions. If a specific column has the same values in multiple rows, it will group these rows.

In the SELECT query, you must use aggregate functions such as COUNT(), MAX(), MIN(), SUM(), AVG(), and so on. The GROUP BY clause produces a separate row for each value of the GROUP BY column.

Key characteristics of GROUP BY are:

1. The GROUP BY clause is used in association with the SELECT command.

2. The GROUP BY clause comes after the WHERE section in the query.

3. If an ORDER BY clause is used, it is put before the GROUP BY clause in the query.

4. The Group BY clause is put before the Having clause in the query.

5. Insert a condition in the having clause.

Syntax

SELECT column1, function_name(column2)
FROM table_name
WHERE condition
GROUP BY column1, column2
ORDER BY column1, column2;

The following figure indicates how the GROUP BY clause works:

The left-hand grid has two columns: Number and furniture. When you apply the GROUP BY clause to the furniture column, it gives a result set that contains the furniture column's unique values:

SELECT Furniture type
FROM furniture

GROUP BY Furniture type;

In implementation, the GROUP BY clause is frequently combined with an aggregate function such as MIN, MAX, AVG, SUM, or COUNT to compute a measure that gives information for each group.

The following example shows how the GROUP BY phrase interacts with the COUNT aggregate function:

In this sample, we group the rows by the furniture column values and use the COUNT function on the number column. The result set contains the unique values of the furniture columns as well as the number of rows that correlate to them.

SELECT Furniture type COUNT(number)
FROM furniture

GROUP BY Furniture type;

Grouping columns are the columns that show in the GROUP BY clause. Because the GROUP BY clause considers all NULL values equal, if a grouping column includes NULL values, all NULL values are summarised into a single group.

Examples

Sample Table are given below

GROUP BY single column

Group By single column, we mean grouping all the rows that have the same number in only that one column. Look at the following query:

SELECT Furniture type SUM(Price)
FROM furniture

GROUP BY Furniture type;

Output

As seen in the preceding output, rows with duplicate Furniture Types are grouped under the same Furniture Type, and their associated Price is the sum of the Price of duplicate rows. To compute the total, the SQL SUM() function is used.

Crack your next tech interview with confidence!

GROUP BY Multiple column

GROUP BY column1, column2 is an example of grouping by numerous columns. This implies grouping all rows with the same values in both columns column1 and column2. Look into the following queries:

SELECT Vehicle, Quantity, Count(*)
FROM Vehicle

GROUP BY Vehicle, Quantity;

Output

Vehicle with the same Vehicle and Quantity are grouped in the same group, as shown in the above output. Those whose only Vehicle is the same but not the Quantity are classified as belonging to distinct groups. So, in this case, we've divided the table into two or more sections.

GROUP BY HAVING Clause

Since the database engine executes the WHERE clause before applying the aggregate function, we are unable to use it in this action to filter the query result based on the group aggregated function result. This is how the HAVING phrase was discovered.

The HAVING clause can only be used in conjunction with the SQL GROUP BY clause. For example, we need the locations listed in more than 1000 but fewer than 10000 user profiles.

SELECT column1, function_name(column2)
FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING condition
ORDER BY column1, column2;

Lets look at example of HAVING Clause

SELECT Furniture type SUM(Price)
FROM furniture

GROUP BY Furniture type;
HAVING SUM(Price)>10000; 

Output

As you observe from the preceding output, only one of the three groups shows in the result-set because the sum of SALARY is higher than 10000. As the condition must be placed on groups rather than columns, we used the HAVING clause to position it here.

GROUP BY Clause With JOIN

We can also use the GROUP BY clause in conjunction with the JOIN clause. As an illustration,

SELECT Furniture.number, Furniture.Furniture Type, Count(Vehicle.Owner Name) AS Owner_count
FROM Furniture
LEFT JOIN Vehicle
ON Furniture.number = Vehicle.number
GROUP BY Furniture.number;

Output

The SQL command here combines the tables Furniture and Vehicles and groups the result set by Number (furniture type). This tells us how many orders each Owner Name has made.

GROUP BY with LIMIT

One thing to keep in mind when grouping by numerous columns: SQL evaluates the aggregations before the LIMIT clause. If you don't group by any columns, you'll get a single row—no issue. If you group by a column that has more unique values than the LIMIT number, the aggregates will be computed, and some rows will simply be omitted from the results.

This is a nice method to do things because you know you'll get the right aggregates. Your findings would be significantly different if SQL reduced the table to 100 rows before performing the aggregations. The above query returns more than 100 rows, so it's an excellent illustration. Try removing the limit and running it again to see what changes.

Conclusions

The GROUP BY Clause is used to combine rows with similar values. In SQL, the SELECT command is combined with the GROUP BY clause. The SELECT statement can use constants, aggregate functions, expressions, and column names in the Group BY section. When the HAVING clause is used to reduce the findings, the GROUP BY Clause is invoked.

Good luck and happy learning!