Using GROUP BY in SQL: A Comprehensive Guide

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Important rule

SQL Execution Order (KEY POINT)

SQL processes clauses in this order:

FROM
WHERE        ← filters rows (no grouping yet)
GROUP BY     ← creates groups
HAVING       ← filters groups
SELECT
ORDER BY

Prerequisite Codes

If you don't have table please create below table and practice it.

My database name is SQLExamples

USE SQLExamples

Create this below Employee table

DROP TABLE Employee
CREATE TABLE Employee(
EmpId INT,
EmpName VARCHAR(25),
EmpAddress VARCHAR(100),
EmpDept VARCHAR(25)
)

Insert data inside table

INSERT INTO Employee VALUES
(1, 'Rambo', 'Kolkata', 'IT'),
(2, 'Rohit', 'Kolkata', 'IT'),
(3, 'Rohon', 'Kolkata', 'ITIS'),
(4, 'Ronok', 'Kolkata', 'ITIS'),
(5, 'Rubin', 'Kolkata', 'ITIS'),
(6, 'Sorif', 'Kolkata', 'ADMIN'),
(7, 'Soriful', 'Kolkata', 'ADMIN'),
(8, 'Sofik', 'Kolkata', 'ADMIN')

SELECT * FROM Employee

Output:

The above code will produce the following result-

Example 1: SQL GROUP BY

Code:

SELECT EmpDept, COUNT(EmpDept) AS DeptCount 
FROM Employee
GROUP BY EmpDept 

Output:

The above code will produce the following result-

Example 2:

Code:

SELECT EmpDept, COUNT(EmpDept) AS DeptCount 
FROM Employee
GROUP BY EmpDept 
ORDER BY EmpDept DESC

Output:

The above code will produce the following result-