Group By Clause in SQL
Group By Clause
Group By clause, in SQL, is used in conjunction with the SELECT statement. It is used to form groups for identical data.
In SELECT statement, Group by clause comes after the WHERE clause and before the ORDER BY clause.
Syntax:
Following is the basic syntax used for Group By clause:
SELECT * FROM tbl_name
WHERE [cond_1]
GROUP BY col_name_1, col_name_2, … , col_name_n
ORDER BY col_name_1, col_name_2
For Example
The current table view of the Customers table is as follows:
| ID | C_Name | Contact_No | City | Status | |
| 1 | Tim Robbins | +44 54 443-4434 | London | [email protected] | Active |
| 2 | James Chris | +44 54 498-3476 | Birmingham | [email protected] | Active |
| 3 | Kevin Sputnik | +44 54 487-6987 | Manchester | [email protected] | Inactive |
| 4 | Richard Butler | +44 54 422-2345 | Birmingham | [email protected] | Active |
| 5 | David McGregor | +44 54 413-0989 | Cardiff | [email protected] | Active |
| 6 | Rita Johns | +44 55 453-4534 | London | [email protected] | Active |
SELECT City, Count(City) as C_Count FROM Customers
WHERE STATUS = ‘ACTIVE’
GROUP BY CITY
ORDER BY City
| City | C_Count |
| Birmingham | 2 |
| Cardiff | 1 |
| London | 1 |
| London | 1 |
In the above example, only one column City is selected and another variable column based on the count value of the City column is created as C_Count. Now after applying Group By command, the query actually made a group of all cities listed in the table having STATUS = ACTIVE. Therefore, in the table, only Birmingham was repeated twice so the C_Count value of Birmingham is 2 and values for rest of the cities are 1.
More Related Articles For You
- Architecture of DBMS
- CODDS RULE DBMS
- Database Models in DBMS
- Relational DBMS Concepts
- Keys and Types of keys in Database
- Database Normalization
- Generalization, Specialization and Aggregation Concepts in DBMS
- ERD Diagram Tutorial with Examples in DBMS
- Introduction to SQL
- How to Create Query in SQL, Create Table, Delete Table and User Insert Info Statements
- Alter Query Command in SQL, Add, Modify and Drop Column in Table
- TCL Commands in SQL
- Truncate Query, Drop and Rename Query in SQL
- All DML Statement in SQL, Select Statement, Delete, Insert and Update Statement
- Data Control Language DCL Revoke and Grant Command in SQL
- Select Statement or Select Query in SQL Explained with Examples
- Distinct Keyword Explained in SQL
- WHERE Statement or WHERE Clause in SQL Statement Explained
- AND & OR Operators in SQL
- LIKE Operator in SQL
- ORDER BY Clause Sorting Explained in SQL
- Having Clause Explained in SQL
- SQL Constraints
- SQL Aggregate Functions
- SQL Aliases Use and Purpose in SQL
- SQL Joins Types, Use and Purpose
- SQL Sequence Syntax and Use with Examples
- SQL View
- SET Operation in SQL, UNION, UNION ALL, Intersect and Minus