How to use group by clause in SQL Server
SQL: GROUP BY Clause
Lets us see how to use the SQL how to use the SQL GROUP BY clause .Following is the syntax and examples given for the reference and DIY use.
Description
The SQL GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.
Syntax
The syntax in the SQL Server for the GROUP BY clause is as following :
SELECT expression1, expression2 ..
aggregate_function (aggregate_expression)
FROM tables
GROUP BY expression1, expression2 ...
SELECT expression1, expression2 ..
aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2 ...
[ORDER BY expression [ ASC | DESC ]];
Parameters or Arguments
- 1.expression
- Expressions can be called as the column value that must be included in the GROUP BY Clause at the end of the SQL statement.
- 2.aggregate_function
- Sql have some aggregate functions like:--
- For example : SUM, COUNT, MIN, MAX, or AVG functions.
- 3.aggregate_expression
- This is the column Name of a table or the expression that the aggregate_function will use in a group by clause.
- 4.table
- The tables that you wish to retrieve records from. In a sql query there should be a table to get data from using a FROM Clause.
- 5.WHERE conditions
- It is an Optional subject. These are conditions that must be met for the records to be selected.
- 6.ORDER BY expression
- Optional. The expression used to sort the records in the result set. If more than one expression is provided, the values should be comma separated.
- 7.ASC
- The expression used to sort the records in an ascending order
- 8.DESC
- Optional. DESC sorts the result set in descending order by expression.
Example - Using GROUP BY with the SUM Function
We have following the example of group by clause with the function of sum the values of a column in sql server query. For example the employee table with Group by clause
Emp_ID
|
Name
|
Salary
|
Dept_id
|
101
|
Aman
|
16000
|
a-1
|
102
|
Geeta
|
12000
|
a-1
|
103
|
Ishani
|
17000
|
b-2
|
104
|
Aashish
|
14000
|
b-2
|
Enter the following SQL statement:
SELECT dept_id, SUM(salary) AS total_salaries FROM employees GROUP BY dept_id;
There will be 2 records selected. These are the results that you should see:
| Dept_id | total_salaries |
|---|---|
| a-1 | 28000 |
| b-1 | 31000 |
In the example we are summing up the salaries of a given department table with employees detail.
Example - Using GROUP BY with the COUNT Function
We have following the example of group by clause with the function of Count the values of a column in sql server query. For example the employee table with Group by clause
Emp_ID
|
Name
|
Salary
|
Dept_id
|
101
|
Aman
|
16000
|
a-1
|
102
|
Geeta
|
12000
|
a-1
|
103
|
Ishani
|
17000
|
b-2
|
104
|
Aashish
|
14000
|
b-2
|
Enter the following SQL statement:
SELECT dept_id, count(salary) AS total_salaries FROM employees GROUP BY dept_id;
There will be 2 records selected. These are the results that you should see:
| Dept_id | total_salaries |
|---|---|
| a-1 | 28000 |
| b-1 | 31000 |
Note
- The GROUP BY Clause uses the aggregate_function in the result set of the SQL Query.
0comments
Post a Comment