SQL query to select SUM of salary by category in SQL Server

PRA

SOLVED CLOSED ACTIVE ANSWERED
User: PRA
Posted: on Jul 23, 2018 11:49 PM
Forum: SQL Server
Answer: 1
Views: 3172

Hi! I have tblPerson in sql with data.

CREATE TABLE [dbo].[tblPerson](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](25) NULL,
	[Category] [int] NULL,
	[Salary] [float] NULL
)

Id	Name	Category	Salary
1	Rustam	1	50
2	Suhrob	2	30
3	Firuz	1	45
4	Sadriddin	3	40
5	Nurullo	2	35

select Name,Category, Salary, SUM(Salary)SumSalary from tblPerson group by Category,Name,Salary

I want use query get below result:

Id	Name	Category	Salary	SumSalary
1	Firuz	1	45	95
2	Rustam	1	50	95
3	Nurullo	2	35	65
4	Suhrob	2	30	65
5	Sadriddin	3	40	40
Summa				200

Download FREE API for Word, Excel and PDF in ASP.Net

pandeyis...

0 2

Reply
ANSWER
User: pandeyism
Replied: on Jul 24, 2018 01:56 AM
Modified: on Jul 24, 2018 02:03 AM
Report

Hi PRA,

Please refer below Query.

For displaying as per your requirement, You have to do using code behind for merging the rows and displaying sum.

SQL

SELECT t1.id,t1.Name,t1.Category,t1.Salary,t2.SumSalary FROM tblPerson t1
INNER JOIN (SELECT Category,SUM(Salary)SumSalary FROM tblPerson GROUP BY Category) t2
ON t1.Category = t2.Category

Output

id	Name	Category	Salary	SumSalary
1	Rustam	1	50	95
3	Firuz	1	45	95
2	Suhrob	2	30	65
5	Nurullo	2	35	65
4	Sadriddin	3	40	40

For merging column refer below article.

For displaying column sum refer below article.

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

SQL query to select SUM of salary by category in SQL Server

Merge GridView Cells or Columns in a Row in ASP.Net using C# VB.Net

Display sum of Columns total in GridView Footer in ASP.Net using C# and VB.Net

Company

Explore

Follow us

Contact Us