Query to Calculate Cumulative Salary

Question

Post reply

Query to Calculate Cumulative Salary

Santhoshkumar KB

SSCertifiable

Points: 5066
More actions
December 22, 2008 at 3:34 am

#126671

Hi,
I need a query to list the Name, Sal and cumulative of the salary, in increasing order of Salary.
here is the details of the table:
CREATE TABLE dbo.employee_san(
ID int,
Name Varchar(50),
Job Varchar(100),
Mgr int,
Sal float
)
SELECT '1','Alesi, James S. ','President','0','4000','10' UNION ALL
SELECT '2','Jack Nicholas','Manager','1','2500','30' UNION ALL
SELECT '3','Andrews, Carl ','Accountant','2','2700','20' UNION ALL
SELECT '4','Balboni, Michael A.L. ','Accountant','6','2650','10' UNION ALL
SELECT '5','Joe Blow ','Salesman','4','3500','40' UNION ALL
SELECT '6','Bruno, Joseph L. ','Manager','1','3200','30' UNION ALL
SELECT '7','Connor, Martin ','Accountant','6','2200','20' UNION ALL
SELECT '8','Coppola, Marc A. ','Accountant','2','2000','10' UNION ALL
SELECT '9','Name','Accountant','6','2100','40' UNION ALL
SELECT '10','DeFrancisco, John A. ','Accountant','6','3200','30' UNION ALL
SELECT '11','Diaz, Ruben , Sr.','','4','3300','10' UNION ALL
SELECT '12','Dilan, Martin Malave ','Accountant','6','3000','20' UNION ALL
SELECT '13','Duane, Thomas K.','Manager','1','3500','40' UNION ALL
SELECT '14','Andrews, Carl ','Accountant','6','2500','30' UNION ALL
SELECT '15','Bruno, Joseph L. ','Salesman','4','2700','10' UNION ALL
SELECT '16','Andrews, Carl ','Accountant','2','2800','20'
I need a output like
Name Sal Cum_Sal
----------------------------
John 1000 1000
Mike 1500 2500
Alan 2000 4500
Matt 3000 7500
Please let me know if more details are required.
Thanks
Thanks,
Santhosh
Human Knowledge Belongs To The World !!

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

This should work

Select Name, Min(Sal), Sum(Sal)

from employee_san

Group by name

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 2

Thanks for the input, though there were some flaws in it that made it a bit unclear what is desired exactly. Here I changed the input a bit, let me know if anything is incorrect: DROP TABLE #employee_san

GO

CREATE TABLE #employee_san(

ID int,

Name Varchar(50),

Job Varchar(100),

Mgr int,

Sal float,

Unknown int

)

GO

INSERT INTO #employee_san (ID, Name, Job, Mgr, Sal, Unknown)

SELECT '1','Alesi, James S. ','President','0','4000','10' UNION ALL

SELECT '2','Jack Nicholas','Manager','1','2500','30' UNION ALL

SELECT '3','Andrews, Carl ','Accountant','2','2700','20' UNION ALL

SELECT '4','Balboni, Michael A.L. ','Accountant','6','2650','10' UNION ALL

SELECT '5','Joe Blow ','Salesman','4','3500','40' UNION ALL

SELECT '6','Bruno, Joseph L. ','Manager','1','3200','30' UNION ALL

SELECT '7','Connor, Martin ','Accountant','6','2200','20' UNION ALL

SELECT '8','Coppola, Marc A. ','Accountant','2','2000','10' UNION ALL

SELECT '9','Name','Accountant','6','2100','40' UNION ALL

SELECT '10','DeFrancisco, John A. ','Accountant','6','3200','30' UNION ALL

SELECT '11','Diaz, Ruben , Sr.','','4','3300','10' UNION ALL

SELECT '12','Dilan, Martin Malave ','Accountant','6','3000','20' UNION ALL

SELECT '13','Duane, Thomas K.','Manager','1','3500','40' UNION ALL

SELECT '14','Andrews, Carl ','Accountant','6','2500','30' UNION ALL

SELECT '15','Bruno, Joseph L. ','Salesman','4','2700','10' UNION ALL

SELECT '16','Andrews, Carl ','Accountant','2','2800','20'

And here I made a query that should come close what you want. Though I couldn't figure out what's the difference between Sal and Cum_Sal and how it should be derived. Is Sal the average?

SELECT Name, SUM(Sal) Cum_Sal

FROM #employee_san

GROUP BY Name

ORDER BY Cum_Sal ASC

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 3

Hi Santhosh,

Try this

SELECT

ee.selId,

ee.Name,

e.Sal,

ee.CumSal

FROM employee_san e

INNER JOIN

(

SELECT Name, MAX(id) selId, SUM(Sal) CumSal

FROM employee_san

GROUP BY name

) AS ee ON e.id = ee.SelId

ORDER BY e.Sal

Hope this is what you wanted is.

Best Regards

Nitin

http://www.EnlinkURL.com

Regards,
Nitin

Vladan SSC-Insane Points: 22072 More actions · Answer 4

Hello Santhosh,

I think that all the posters have missed the main point, and that is that Cum_Sal is a running total of the output. As I understand it, there is only one row for each person in your table, so no grouping is needed, and you want to display salary of the person in column Sal, and the running total in column Cum_Sal. Is that what you need?

If yes, please search the forum and articles on this site for "running total" and you'll get lots of info about how to do it.

Probably the best article is this one : Jeff Moden : Solving the "Running Total" & "Ordinal Rank" Problems [/url]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

This is what Vladan is talking about...

--===== Move just the data we want to a place where we can work on it

SELECT ISNULL(Sal,0) AS Sal,

ISNULL(Name,CAST('' AS VARCHAR(100)) AS Name,

CAST(0 AS FLOAT) AS Cum_Sal

INTO #Results

FROM dbo.employee_san

--===== Add a very important index to control the processing order

ALTER TABLE #Results

ADD PRIMARY KEY CLUSTERED (Sal,Name)

--===== Calculate the running total in the results table

DECLARE @PrevCum_Sal FLOAT,

@PrevName VARCHAR(100)

SELECT @PrevCum_Sal = 0,

@PrevName = ''

UPDATE #Results

SET @PrevCum_Sal = Cum_Sal = Sal + @PrevCum_Sal,

@PrevName = Name --Just an "anchor"

FROM #Results WITH(INDEX(0))

--===== Display the final result

SELECT Name, Sal, Cum_Sal

FROM #Results

ORDER BY Cum_Sal

Yes, you could do it with just a correlated sub-query that uses a triangular join... very, very bad for performance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 6

r.hensbergen (12/22/2008)
Thanks for the input, though there were some flaws in it that made it a bit unclear what is desired exactly. Here I changed the input a bit, let me know if anything is incorrect:
DROP TABLE #employee_san
GO
CREATE TABLE #employee_san(
ID int,
Name Varchar(50),
Job Varchar(100),
Mgr int,
Sal float,
Unknown int
)

The Unknown column is DEPT

SELECT Name, SUM(Sal) Cum_Sal
FROM #employee_san
GROUP BY Name
ORDER BY Cum_Sal ASC

I need to get Running Total as Vladan said below

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 7

Jeff Moden (12/22/2008)
This is what Vladan is talking about...
--===== Move just the data we want to a place where we can work on it
SELECT ISNULL(Sal,0) AS Sal,
ISNULL(Name,CAST('' AS VARCHAR(100)) AS Name,
CAST(0 AS FLOAT) AS Cum_Sal
INTO #Results
FROM dbo.employee_san
--===== Add a very important index to control the processing order
ALTER TABLE #Results
ADD PRIMARY KEY CLUSTERED (Sal,Name)
--===== Calculate the running total in the results table
DECLARE @PrevCum_Sal FLOAT,
@PrevName VARCHAR(100)
SELECT @PrevCum_Sal = 0,
@PrevName = ''
UPDATE #Results
SET @PrevCum_Sal = Cum_Sal = Sal + @PrevCum_Sal,
@PrevName = Name --Just an "anchor"
FROM #Results WITH(INDEX(0))
--===== Display the final result
SELECT Name, Sal, Cum_Sal
FROM #Results
ORDER BY Cum_Sal

Yes this worked fine.

Thank You

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 8

Jeff Moden (12/22/2008)
This is what Vladan is talking about...
--===== Move just the data we want to a place where we can work on it
SELECT ISNULL(Sal,0) AS Sal,
ISNULL(Name,CAST('' AS VARCHAR(100)) AS Name,
CAST(0 AS FLOAT) AS Cum_Sal
INTO #Results
FROM dbo.employee_san

--===== Add a very important index to control the processing order

ALTER TABLE #Results

ADD PRIMARY KEY CLUSTERED (Sal,Name)

[/quote-1]

Why is this composite key declared?

--===== Calculate the running total in the results table

DECLARE @PrevCum_Sal FLOAT,

@PrevName VARCHAR(100)

SELECT @PrevCum_Sal = 0,

@PrevName = ''

UPDATE #Results

SET @PrevCum_Sal = Cum_Sal = Sal + @PrevCum_Sal,

@PrevName = Name --Just an "anchor"

FROM #Results WITH(INDEX(0))

--===== Display the final result

SELECT Name, Sal, Cum_Sal

FROM #Results

ORDER BY Cum_Sal

[/quote-2]

How is that Update #Results statement working here?

Thanks

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 9

UPDATE #Results
SET @PrevCum_Sal = Cum_Sal = Sal + @PrevCum_Sal,
@PrevName = Name --Just an "anchor"
FROM #Results WITH(INDEX(0))
--===== Display the final result
SELECT Name, Sal, Cum_Sal
FROM #Results
ORDER BY Cum_Sal
How is that Update #Results statement working here?
Thanks

Yes, it also wondered me how powerful this update statement is. I didn't do any testing on it, but is it correct that with this kind of code

SET @a = A + @a

@b-2 = Code

FROM #Test

sums up the A column in the @a variable as long as the Code column stays the same?

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

vbandlamudi Mr or Mrs. 500 Points: 542 More actions · Answer 10

Correct Answer

Select id,name, sal, cumsal=

(select sum(t1.sal)

from employee_san t1

where t1.id <= t.id)

from employee_san t

vbandlamudi Mr or Mrs. 500 Points: 542 More actions · Answer 11

Select id,name, sal, cumsal=

(select sum(t1.sal)

from employee_san t1

where t1.id <= t.id)

from employee_san t

vbandlamudi Mr or Mrs. 500 Points: 542 More actions · Answer 12

Select id,name, sal, cumsal=

(select sum(t1.sal)

from employee_san t1

where t1.id <= t.id)

from employee_san t

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 13

vbandlamudi (12/23/2008)
Correct Answer
Select id,name, sal, cumsal=
(select sum(t1.sal)
from employee_san t1
where t1.id <= t.id)
from employee_san t

This works fine.

But the salary is not ORDERed BY ASC

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 14

vbandlamudi (12/23/2008)
Correct Answer
Select id,name, sal, cumsal=
(select sum(t1.sal)
from employee_san t1
where t1.id <= t.id)
from employee_san t

Ummmm... No... that's NOT the correct answer. That makes a triangular join. Try that on just 20,000 rows and see how long it takes. Then, read the following to find out why it takes that long...

[font="Arial Black"]Hidden RBAR: Triangular Joins[/font][/url]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)