We can easily find the department wise total salary of employees from the following two table tbl_emp and tbl_dept. We used GROUP BY clause and INNER JOIN for this purpose.
-- Create Table tbl_dept
CREATE TABLE [dbo].[tbl_dept](
[DeptCode] [int] PRIMARY KEY,
[DeptName] [nvarchar](50) NOT NULL,
)
GO
-- Insert data in the Table tbl_dept
INSERT [dbo].[tbl_dept] ([DeptCode], [DeptName]) VALUES (1, N'IT')
GO
INSERT [dbo].[tbl_dept] ([DeptCode], [DeptName]) VALUES (2, N'Account')
GO
INSERT [dbo].[tbl_dept] ([DeptCode], [DeptName]) VALUES (3, N'HR')
GO
INSERT [dbo].[tbl_dept] ([DeptCode], [DeptName]) VALUES (4, N'Finance')
GO
INSERT [dbo].[tbl_dept] ([DeptCode], [DeptName]) VALUES (5, N'Security')
GO
-- Select data from tbl_dept
SELECT * FROM [dbo].[tbl_dept]
GO
-- Create Table tbl_emp
CREATE TABLE [dbo].[tbl_emp](
[EmpCode] [int] PRIMARY KEY,
[EmpName] [nvarchar](50) NOT NULL,
[EmpSalary] [float] NOT NULL,
[DeptCode] [int] FOREIGN KEY REFERENCES tbl_dept([DeptCode]),
)
GO
-- Insert data in the Table tbl_emp
INSERT [dbo].[tbl_emp] ([EmpCode], [EmpName], [EmpSalary], [DeptCode]) VALUES (1, N'Peter', 1000, 3)
GO
INSERT [dbo].[tbl_emp] ([EmpCode], [EmpName], [EmpSalary], [DeptCode]) VALUES (2, N'Robin', 1200, 2)
GO
INSERT [dbo].[tbl_emp] ([EmpCode], [EmpName], [EmpSalary], [DeptCode]) VALUES (3, N'Sam', 1000, 3)
GO
INSERT [dbo].[tbl_emp] ([EmpCode], [EmpName], [EmpSalary], [DeptCode]) VALUES (5, N'Jack', 1000, 1)
GO
INSERT [dbo].[tbl_emp] ([EmpCode], [EmpName], [EmpSalary], [DeptCode]) VALUES (6, N'David', 750, 1)
GO
INSERT [dbo].[tbl_emp] ([EmpCode], [EmpName], [EmpSalary], [DeptCode]) VALUES (7, N'Tina', 1100, 4)
GO
INSERT [dbo].[tbl_emp] ([EmpCode], [EmpName], [EmpSalary], [DeptCode]) VALUES (8, N'James', 1100, 4)
GO
INSERT [dbo].[tbl_emp] ([EmpCode], [EmpName], [EmpSalary], [DeptCode]) VALUES (9, N'Daniel', 950, 1)
GO
-- Select data from tbl_emp
SELECT * FROM [dbo].[tbl_emp]
GO
-- Department wise total salary
SELECT a.DeptName, SUM(b.EmpSalary) AS TotalAmount
FROM tbl_dept a INNER JOIN tbl_emp b
ON a.DeptCode=b.DeptCode
GROUP BY a.DeptName
ORDER BY TotalAmount DESC
GO
