T-SQL - GROUP BY Clause

The SQL Server GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups.

The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.

Syntax

Following is the basic syntax of GROUP BY clause. The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.

SELECT column1, column2 
FROM table_name 
WHERE [ conditions ] 
GROUP BY column1, column2 
ORDER BY column1, column2

Example

Consider the CUSTOMERS table is having the following records −

ID  NAME       AGE       ADDRESS          SALARY 
1   Ramesh     32        Ahmedabad        2000.00 
2   Khilan     25        Delhi            1500.00 
3   kaushik    23        Kota             2000.00 
4   Chaitali   25        Mumbai           6500.00 
5   Hardik     27        Bhopal           8500.00 
6   Komal      22        MP               4500.00 
7   Muffy      24        Indore           10000.00

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

SELECT NAME, SUM(SALARY) as [sum of salary] FROM CUSTOMERS 
   GROUP BY NAME;

The above command will produce the following output.

NAME        sum of salary 
Chaitali    6500.00 
Hardik      8500.00 
kaushik     2000.00 
Khilan      1500.00 
Komal       4500.00 
Muffy       10000.00 
Ramesh      2000.00

Let us now consider the following CUSTOMERS table having the following records with duplicate names.

ID  NAME       AGE       ADDRESS           SALARY 
1   Ramesh     32        Ahmedabad         2000.00 
2   Khilan     25        Delhi             1500.00 
3   kaushik    23        Kota              2000.00 
4   Chaitali   25        Mumbai            6500.00 
5   Hardik     27        Bhopal            8500.00 
6   Komal      22        MP                4500.00 
7   Muffy      24        Indore            10000.00

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

SELECT NAME, SUM(SALARY) as [sum of salary] FROM CUSTOMERS 
   GROUP BY NAME

The above command will produce the following output.

NAME        sum of salary 
Hardik      8500.00 
kaushik     8500.00 
Komal       4500.00 
Muffy       10000.00 
Ramesh      3500.00
Advertisements