![SQL Logo](https://www.tutorialspoint.com/sql/images/sql-mini-logo.jpg)
- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Database
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Comments
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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 | +-----+--------------+