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 Email Status
1 Tim Robbins +44 54 443-4434 London tim@myemail.uk Active
2 James Chris +44 54 498-3476 Birmingham james@myemail.uk Active
3 Kevin Sputnik +44 54 487-6987 Manchester kevin@myemail.uk Inactive
4 Richard Butler +44 54 422-2345 Birmingham richard@myemail.uk Active
5 David McGregor +44 54 413-0989 Cardiff david@myemail.uk Active
6 Rita Johns +44 55 453-4534 London rita@myemail.uk 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

    DBMS Tutorial

    SQL and DBMS Interview Questions with Answers

    DBMS and SQL Practice Tests , Questions and Quizzes

Search

Shortcodes Ultimate

 
Follow Us
 
BGP