SQL – Group By

The SQL GROUP BY clause is used in conjunction with the SELECT statement to arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY or HAVING clause (if they exist). It is often used with aggregate functions like SUM, COUNT, AVG, MAX, or MIN, which allows us to perform calculations on the grouped data.

For example, let us suppose you have a table of sales data consisting of date, product, and sales amount. To calculate the total sales of an organization in an year, the GROUP BY clause can be used to group the sales of products together. Similarly, you can group the data by date to calculate the total sales for each day, or by a combination of product and date to calculate the total sales for each product on each day.

Syntax

Following is the basic syntax of a GROUP BY clause −

SELECT column_name(s)
FROM table_name
GROUP BY column_name(s);

Where, column_name(s) refers to the name of one or more columns in the table that we want to group the data by and the table_name refers to the name of the table that we want to retrieve data from.

GROUP BY with Single Column

When we use the GROUP BY clause with a single column, SQL will place all rows in the table that have the same value in that particular column into a single record.

Example

Assume we have created a table with name CUSTOMERS in SQL database using CREATE TABLE statement as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Following query inserts values into this table using the INSERT statement −

insert INTO CUSTOMERS VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00);
insert INTO CUSTOMERS VALUES(2, 'Khilan', 25, 'Delhi', 1500.00);
insert INTO CUSTOMERS VALUES(3, 'kaushik', 23, 'Kota', 2000.00);
insert INTO CUSTOMERS VALUES(4, 'Chaitali', 25, 'Mumbai', 6500.00);
insert INTO CUSTOMERS VALUES(5, 'Hardik', 27, 'Bhopal', 8500.00);
insert INTO CUSTOMERS VALUES(6, 'Komal', 22, 'MP', 4500.00);
insert INTO CUSTOMERS VALUES(7, 'Muffy', 24, 'Indore', 10000.00);

If we verify the contents of the CUSTOMERS table using the SELECT statement, we can observe the inserted records as shown below −

SELECT * from CUSTOMERS;
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Now,we are trying to group the customers by their age and calculate the average salary for each age using the following query −

SELECT AGE, AVG(SALARY) as avg_salary
FROM CUSTOMERS
GROUP BY age

Output

This would produce the following result −

+-----+--------------+
| AGE | avg_salary   |
+-----+--------------+
|  22 |  4500.000000 |
|  23 |  2000.000000 |
|  24 | 10000.000000 |
|  25 |  4000.000000 |
|  27 |  8500.000000 |
|  32 |  2000.000000 |
+-----+--------------+

GROUP BY with Multiple Columns

When we use the GROUP BY clause with multiple columns, SQL will place all rows in the table that have the same values in all of the specified columns into a single group.

Example

Now, let us look at a table where the CUSTOMERS table has the following records with duplicate records −

+----+----------+-----+--------+----------+
| ID | NAME     | AGE | Gender | SALARY   |
+----+----------+-----+--------+----------+
|  1 | Ramesh   |  25 | Male   |  2000.00 |
|  2 | Ramesh   |  25 | Male   |  1500.00 |
|  3 | kaushik  |  25 | Female |  2000.00 |
|  4 | kaushik  |  20 | Male   |  6500.00 |
|  5 | Hardik   |  25 | Male   |  8500.00 |
|  6 | Komal    |  20 | Female |  4500.00 |
|  7 | Muffy    |  25 | Male   | 10000.00 |
+----+----------+-----+--------+----------+

Now again, if you want to know the total amount of salary for each gender in each age, then the GROUP BY query would be as follows −

SELECT gender, age, SUM(salary) AS total_salary
FROM Custome
GROUP BY gender, age

Output

This would produce the following result −

+--------+-----+--------------+
| gender | age | total_salary |
+--------+-----+--------------+
| Female |  20 |      4500.00 |
| Male   |  20 |      6500.00 |
| Female |  25 |      2000.00 |
| Male   |  25 |     22000.00 |
+----------+-----+------------+

GROUP BY with Aggregate Functions

When using the GROUP BY clause in SQL, we can also apply aggregate functions such as SUM, AVG, MIN, MAX, COUNT, etc. to the columns in the SELECT statement that are not included in the GROUP BY clause.

Example

The following SQL query lists the number of customers in each age −

SELECT COUNT(Name) as Net_cust, AGE
FROM Customers
GROUP BY AGE

Output

Following is the result produced −

+----------+-----+
| Net_cust | AGE |
+----------+-----+
|        1 |  22 |
|        1 |  23 |
|        1 |  24 |
|        2 |  25 |
|        1 |  27 |
|        1 |  32 |
+----------+-----+

Example

In the following query, we are trying to find the highest salary for each age −

SELECT AGE, MAX(salary) AS max_salary
FROM CUSTOMERS
GROUP BY AGE

Output

Following is the output of the above query −

+-----+------------+
| AGE | max_salary |
+-----+------------+
|  22 |   4500.00  |
|  23 |   2000.00  |
|  24 |  10000.00  |
|  25 |   6500.00  |
|  27 |   8500.00  |
|  32 |   2000.00  |
+-----+------------+

GROUP BY with ORDER BY clause

We can use the ORDER BY clause with GROUP BY in SQL to sort the result set by one or more columns.

Syntax

Following is the syntax for using ORDER BY clause with GROUP BY clause in SQL −

SELECT column1, column2, ..., aggregate_function(columnX) AS alias
FROM table
GROUP BY column1, column2, ...
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

Example

In here, we are trying to find the highest salary for each age, sorted by high to low −

SELECT AGE, MAX(salary) AS max_salary
FROM CUSTOMERS
GROUP BY AGE
ORDER BY MAX(salary) DESC

Output

Following is the result produced −

+-----+------------+
| AGE | max_salary |
+-----+------------+
|  24 |   10000.00 |
|  27 |    8500.00 |
|  25 |    6500.00 |
|  22 |    4500.00 |
|  23 |    2000.00 |
|  32 |    2000.00 |
+-----+------------+

GROUP BY with HAVING clause

We can also use the GROUP BY clause with the HAVING clause to filter the results of a query based on conditions applied to groups of data. The condition can be applied to an aggregate function that is used in the SELECT statement or to a column in the GROUP BY clause.

Syntax

Following is the syntax for using ORDER BY clause with HAVING clause in SQL −

SELECT column1, column2, aggregate_function(column)
FROM table_name
GROUP BY column1, column2
HAVING condition;

Example

In the following query, we are grouping the customers by their age and calculating the average salary for each group. The HAVING clause is used to filter the results to show only those groups where the average salary is greater than 8000 −

SELECT AGE, AVG(salary) AS avg_salary
FROM CUSTOMERS
GROUP BY AGE
HAVING AVG(salary) > 8000

Output

The result produced is as follows −

+-----+--------------+
| AGE | avg_salary   |
+-----+--------------+
|  24 | 10000.000000 |
|  27 |  8500.000000 |
+-----+--------------+