SUM with CONDITIONs using GROUP BY or PARTITION 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)

SUM with CONDITIONs using GROUP BY or PARTITION BY

Author

Topic

rasta
Starting Member

23 Posts

Posted - 2012-01-24 : 07:24:06

I have several records with the same ClientID and bus_dt. In such a case I would like to select the row with higher value of DniPoSpl. Moreover, if TYPE differs (e.g. 'A', 'B') I would like to make the SUM of DniPoSPl of the row with the same ClientID and bus_dt and write this value instead of the value DniPoSpl of the chosen row.

Here is the example:

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')

The sample output should be as following:

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

(eg.: there is only one record for date 2011-03-30, so I can choose only this row. 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, in case TYPEs do not differ I would not make sum).)

Maybe OVER PARTITION BY might help??? Thanks.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

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

Repost of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170548

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