Home » T-SQL GROUP BY

T-SQL GROUP BY

by Online Tutorials Library

T-SQL GROUP BY

In Transact SQL, GROUP BY clause is used to arrange the data into group. It is followed by WHERE clause into the SELECT statement in the query. It has aggregate functions (MAX, MIN, AVG, SUM and COUNT) to group the result by one or many columns.

Syntax of GROUP By Clause is below:

The GROUP BY clause follows the conditions in the WHERE clause and introduces the ORDER BY clause.

Example:

Consider the CUSTOMERS table that has the following records –

ID NAME AGE ADDRESS SALARY
01 William Shakespeare 32 Karachi 7000.00
02 Alpoura 24 London 3000.00
03 Jackson 34 Paris 1200.00
04 HarperMachmohan 20 New York 1500.00
05 EllaDevgun 22 Islamabad 3400.00
06 David Warner 23 Turkey 4400.00
07 Mason 26 Saudi Arabia 5050.00

If you want to know the total amount of salary of each customer, then the following will be the GROUP BY query.

The command gives the output, which is given below.

NAME SALARY
William Shakespeare 7000.00
Alpoura 3000.00
Jackson 1200.00
Harper Machmohan 1500.00
Ella Devgun 3400.00
David Warner 4400.00
Mason 5050.00

Let us consider the following CUSTOMERS table, which has the records with different names.

ID NAME AGE ADDRESS SALARY
01 William Shakespeare 32 Karachi 7000.00
02 Alpoura 24 London 3000.00
03 Jackson 34 Paris 1200.00
04 HarperMachmohan 20 New York 1500.00
05 EllaDevgun 22 Islamabad 3400.00
06 David Warner 23 Turkey 4400.00
07 Mason 26 Saudi Arabia 5050.00

If we want to know the total amount of salary of each customer, then the following GROUP BY query is generated.

The command give the below output.

ID AGE SALARY
Alpoura 24 7400.00
Ella Devgun 22 3400.00
Harper Machmohan 20 2700.00
Mason 26 5050.00
William Shakespeare 32 10000.00

You may also like