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.
Author |
Topic |
fholemans
Starting Member
12 Posts |
Posted - 2012-11-08 : 08:51:09
|
Hi, I have a question regarding the addition in SQL. Indeed, I will sum the data of a dimension with multiple members in only one member of this dimension. I explain. I have the dimension DIRECTIONS with 5 members: Z_STAT, DIR_FSCR, DIR_RH, DIR_FIN and DIR_ENS.The goal is to sum all the data of the 5 members into an unique.To a better understanding, I show a example with numbers : Now I have data on: M_ACCOUNT DIRECTIONS VALUE M606 Z_STAT 1200M606 DIR_FSCR -100M606 DIR_RH -200M606 DIR_FIN -50M606 DIR_ENS -70And the goal is to have: M606 Z_STAT 780Thanks in advance. |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-08 : 09:13:05
|
I'm assuming you chose Z_STAT since it had the largest valueSELECT t1.Account,t3.Directions,sum(t1.Value) as ValueFROM yourTable t1CROSS APPLY( select top 1 Directions,value from yourTable t2 where t1.Account = t2.Account order by value desc) t3GROUP BY t1.Account,t3.DirectionsJimEveryday I learn something that somebody else already knew |
 |
|
fholemans
Starting Member
12 Posts |
Posted - 2012-11-08 : 09:51:53
|
I don't understand what represent t1.Account and t3.directionsMaybe it will be more understandable :For now, I have this data in my database. But the goal is to have this Here SIGNEDDATA represents the sum of the 5 SIGNEDDATA aboveMy table name is tblFactMGMTAPP.Thanks |
 |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-09 : 00:14:16
|
[code]SELECT t1.*, t2.DirectionsFROM (SELECT category, groups, timeId, SUM(SignedData) SumSignedData, Source, I_PISTE, M_Account, M_Satasrc, Pistes, SubTables FROM tblFactMGMTAPP GROUP BY category, groups, timeId, Source, I_PISTE, M_Account, M_Satasrc, Pistes, SubTables ) t1JOIN (SELECT top 1 Category, M_account, Directions FROM tblFactMGMTAPP) t2ON t1.category = t2.category AND t1.m_account = t2.m_account[/code]--Chandu |
 |
|
|
|
|
|
|