How SUM 2 rows using OVER PARTITION BY...ORDER BY

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How SUM 2 rows using OVER PARTITION BY...ORDER BY

Author

Topic

rasta
Starting Member

23 Posts

Posted - 2012-01-23 : 09:55:24

I would like to select 1st row per given ClientID and bus_dt
using PARTITION BY...ORDER BY algorithm
WHILE giving the SUM of DniPoSPl WHERE TYPE for the same ClientID and bus_dt is not equal (TYPE = A and B for the same ClientID and bus_dt):

Create table tmp_portfolio (ID int not null identity, ClientID int not null, bus_dt datetime not null, DniPoSPl int null,TYPE nvarchar(8) null)
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl,TYPE)
Values (1, '2011-03-30',null,'A')
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl,TYPE)
Values (1, '2011-04-30',null,'A')
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl,TYPE)
Values (1, '2011-04-30' , 640,'B')
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl,TYPE)
Values (1, '2011-06-30' ,821,'A')
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl,TYPE)
Values (2, '2010-06-30' , null,'A')
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl,TYPE)
Values (2, '2010-07-31' ,5,'A')
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl,TYPE)
Values (2, '2010-07-31' ,2,'B')
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl,TYPE)
Values (2, '2010-09-30', 40,'B')
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl,TYPE)
Values (2, '2010-09-30' , 943,'B')
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl,TYPE)
Values (2, '2010-11-30' , 1127,'A')
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl,TYPE)
Values (2, '2010-11-30' , 1308,'A')
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl,TYPE)
Values (2, '2011-01-31' , 1492,'A')
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl,TYPE)
Values (2, '2011-01-31' ,1673,'B')

???:
WITH numbered AS (SELECT *, rn=ROW_NUMBER()
OVER (PARTITION BY ClientID, bus_dt ORDER BY ID ASC)
FROM tmp_portfolio AS m )
SELECT * INTO tmp_portfolio2 FROM numbered WHERE rn=1

rasta
Starting Member

23 Posts

Posted - 2012-01-23 : 10:53:35

I am trying the following without success:

WITH numbered AS (SELECT *, rn=ROW_NUMBER()
OVER (PARTITION BY ClientID, bus_dt ORDER BY ID ASC)
FROM tmp_portfolio AS m )
SELECT ClientID, bus_dt,CASE WHEN unique(TYPE) = 2 THEN SUM(DniPoSpl) END, TYPE INTO tmp_portfolio2 FROM numbered WHERE rn=1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-23 : 11:19:02

To make it more clear it would be SUPER to post the wanted result in relation to the given sample data.

No, you're never too old to Yak'n'Roll if you're too young to die.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-01-23 : 11:25:05

Does this give you what you are looking for? If not, can you give us some sample output?

select 
	ClientID, SUM(DniPoSPl) as tot, bus_dt
from 
	#tmp_portfolio 
group by
	ClientID,bus_dt
order by
	ClientID 

OUTPUT
ClientID	tot	bus_dt
1	NULL	2011-03-30 00:00:00.000
1	640	2011-04-30 00:00:00.000
1	821	2011-06-30 00:00:00.000
2	NULL	2010-06-30 00:00:00.000
2	7	2010-07-31 00:00:00.000
2	983	2010-09-30 00:00:00.000
2	2435	2010-11-30 00:00:00.000
2	3165	2011-01-31 00:00:00.000

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

rasta
Starting Member

23 Posts

Posted - 2012-01-23 : 12:43:16

Thanks. I prefer to use OVER PARTITION BY procedure where SUM DniPoSpl if TYPEs for the same bus_dt differs (and return the raw of <ID), if not return the raw with higher DniPoSpl:
The sample output should be as following:

ClientID bus_dt DniPoSPl TYPE
1 2011-03-30 00:00:00.000 NULL A
1 2011-04-30 00:00:00.000 640 A
1 2011-06-30 00:00:00.000 821 A
2 2010-07-31 00:00:00.000 7 A
2 2010-09-30 00:00:00.000 943 B
2 2010-11-30 00:00:00.000 1308 A
2 2011-01-31 00:00:00.000 3165 A

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-01-23 : 14:32:21

So why the null for 2011-03-30, but not for 2010-06-30?

Why a sum of 943 for B and 2010-09-30 when there are 2 records to add together for that date?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

rasta
Starting Member

23 Posts

Posted - 2012-01-24 : 05:10:53

There is only one record for 2011-03-30, so I take this one. On the other hand, there are two records for 2010-09-30. Firstly, these are filtered by DniPoSpl DESC (I choose row with higher value), secondly, there are different types 'A' and 'B' (if TYPEs differs I write SUM of them into the chosen row).

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-01-24 : 08:43:34

You cannot choose the row with the higher value and call it a sum. That is a MAX, not a SUM.
In your output, everything is a MAX except the last row, which is a SUM. Your logic is not consistent.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

rasta
Starting Member

23 Posts

Posted - 2012-01-24 : 09:59:32

What about creating a new column with SUM such that rows with the same ClientID and bus_dt will have the same value of the SUM and than choose the row according to higher value of DniPoSPl?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-01-24 : 10:45:48

What you are saying makes no sense. Why would you need a SUM if you are just looking for the MAX any ways?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources