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:

<span class="pln">
SELECT column_name</span><span class="pun">(</span><span class="pln">s</span><span class="pun">)</span><span class="pln">
FROM table_name
WHERE condition
GROUP BY column_name</span><span class="pun">(</span><span class="pln">s</span><span class="pun">)</span><span class="pln">
ORDER BY column_name</span><span class="pun">(</span><span class="pln">s</span><span class="pun">);</span><span class="pln">
</span>

Prerequisite Codes

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

My database name is SQLExamples

<span class="pln">

USE </span><span class="typ">SQLExamples</span><span class="pln">
</span>

Create this below Employee table

<span class="pln">
DROP TABLE </span><span class="typ">Employee</span><span class="pln">
CREATE TABLE </span><span class="typ">Employee</span><span class="pun">(</span><span class="pln">
</span><span class="typ">EmpId</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln">
</span><span class="typ">EmpName</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">25</span><span class="pun">),</span><span class="pln">
</span><span class="typ">EmpAddress</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">100</span><span class="pun">),</span><span class="pln">
</span><span class="typ">EmpDept</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">25</span><span class="pun">)</span><span class="pln">
</span><span class="pun">)</span><span class="pln">
</span>

Insert data inside table

<span class="pln">
INSERT INTO </span><span class="typ">Employee</span><span class="pln"> VALUES
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rambo'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'IT'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rohit'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'IT'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rohon'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ITIS'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">4</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Ronok'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ITIS'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">5</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rubin'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ITIS'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">6</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Sorif'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ADMIN'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">7</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Soriful'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ADMIN'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">8</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Sofik'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ADMIN'</span><span class="pun">)</span><span class="pln">

</span>

<span class="pln">
SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Employee</span><span class="pln">
</span>

Output:

The above code will produce the following result-

Example 1: SQL GROUP BY

Code:

<span class="pln">
SELECT </span><span class="typ">EmpDept</span><span class="pun">,</span><span class="pln"> COUNT</span><span class="pun">(</span><span class="typ">EmpDept</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">DeptCount</span><span class="pln"> 
FROM </span><span class="typ">Employee</span><span class="pln">
GROUP BY </span><span class="typ">EmpDept</span><span class="pln"> 
</span>

Output:

The above code will produce the following result-

Example:

Code:

<span class="pln">
SELECT </span><span class="typ">EmpDept</span><span class="pun">,</span><span class="pln"> COUNT</span><span class="pun">(</span><span class="typ">EmpDept</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">DeptCount</span><span class="pln"> 
FROM </span><span class="typ">Employee</span><span class="pln">
GROUP BY </span><span class="typ">EmpDept</span><span class="pln"> 
ORDER BY </span><span class="typ">EmpDept</span><span class="pln"> DESC
</span>

Output:

The above code will produce the following result-