Sql Group By Clause is used to group similar type of data on the basis of common column that common between them. We can group one or more column in Group By clause. In group by clause we can use aggregate function that is Max, Min, Sum, Count and Average.
SQL
GROUP BY Syntax
SELECT
column_name, aggregate_function(column_name)
FROM table_name
WHERE
column_name operator value
GROUP BY column_name;
Parameters:
Column_List_Item
Specifies one or more columns used to group rows returned by the query. Column_List_Item can be one of the following:
1.
A field in a table in the FROM clause or a
subquery.
2.
A table alias from the SQL SELECT list.
3.
A numeric expression indicating the location of the
column in the result table. The leftmost column is number 1.
Table Structure and Insert
Query
CREATE TABLE [dbo].[Employee]
(
[Employee_id] [int] IDENTITY(1,1) NOT NULL,
[First_name] [nvarchar](300) NULL,
[Last_name] [nvarchar](300) NULL,
[Salary] [decimal](20, 2) NULL,
[Joining_date] [datetime] NULL,
[Department] [nvarchar](200) NULL,
[Department_ID] [int] NULL,
[City] [nvarchar](250) NULL
) ON [PRIMARY]
INSERT [dbo].[Employee] ([First_name],
[Last_name], [Salary],
[Joining_date], [Department], [Department_ID],
[City]) VALUES ('John', 'Abraham', 1000000.00, 1905-06-07, 'Banking', 1, 'Delhi')
INSERT [dbo].[Employee] ([Last_name],
[Salary], [Joining_date], [Department], [Department_ID], [City]) VALUES ('Michael', 'Clarke', 800000.00, 1894-06-28, 'Insurance', 2, 'Mumbai')
INSERT [dbo].[Employee] ([First_name],
[Last_name], [Salary],
[Joining_date], [Department], [Department_ID],
[City]) VALUES ('Michael', 'Clarke', 800000.00, 1894-06-28, 'Insurance', 1, 'Delhi')
INSERT [dbo].[Employee] ([First_name],
[Last_name], [Salary],
[Joining_date], [Department], [Department_ID],
[City]) VALUES ('Roy', 'Thomas', 700000.00, 1894-06-27, 'Banking', 1, 'Mumbai')
INSERT [dbo].[Employee] ([First_name],
[Last_name], [Salary],
[Joining_date], [Department], [Department_ID],
[City]) VALUES ('Tom', 'Jose', 600000.00, 1894-06-27, 'Insurance', 3, 'kolkata')
INSERT [dbo].[Employee] ([First_name],
[Last_name], [Salary],
[Joining_date], [Department], [Department_ID],
[City]) VALUES ('Jerry', 'Pinto', 650000.00, 1894-06-27, 'Insurance', 3, 'Delhi')
INSERT [dbo].[Employee] ([First_name],
[Last_name], [Salary],
[Joining_date], [Department], [Department_ID],
[City]) VALUES ('Philip', 'Mathew',750000.00, 1894-06-28, 'Services', 2, 'kolkata')
INSERT [dbo].[Employee] ([First_name],
[Last_name], [Salary],
[Joining_date], [Department], [Department_ID],
[City]) VALUES ('TestName1', '123', 650000.00, 1894-06-28, 'Services', 2, 'Delhi')
INSERT [dbo].[Employee] ([First_name],
[Last_name], [Salary],
[Joining_date], [Department], [Department_ID],
[City]) VALUES ('TestName2', 'Lname%',600000.00, 1894-06-27, 'Insurance', 2, 'Mumbai')
Example1:
If you want to know the total number of Employee in each
department, the query would be:
SELECT Department,COUNT(employee_id)as [Total Employee]
FROM Employee
GROUP BY Department
Example2:
If you want to know the total salary in each department, the
query would be:
SELECT Department,SUM(Salary)as [Department Wise Total Salary]
FROM Employee
GROUP BY Department
Example3:
If you want to know the Department and City wise total
salary, the query would be:
--Department and
City wise SUM of Salary
SELECT
Department,City, SUM(salary) as [Department and City Wise Total Salary]
FROM Employee
GROUP BY Department, City;
