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 |