SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Addition of Dimensions In only one		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

fholemans
Starting Member

Belgium
12 Posts
Posted - 11/08/2012 :  08:51:09  Show Profile  Reply with Quote
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 1200
M606 DIR_FSCR -100
M606 DIR_RH -200
M606 DIR_FIN -50
M606 DIR_ENS -70

And the goal is to have:

M606 Z_STAT 780


Thanks in advance.

jimf
Flowing Fount of Yak Knowledge

USA
2870 Posts
Posted - 11/08/2012 :  09:13:05  Show Profile  Reply with Quote
I'm assuming you chose Z_STAT since it had the largest value

SELECT t1.Account,t3.Directions,sum(t1.Value) as Value
FROM yourTable t1
CROSS APPLY
( select top 1 Directions,value
from yourTable t2
where t1.Account = t2.Account
order by value desc
) t3

GROUP BY t1.Account,t3.Directions

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

fholemans
Starting Member

Belgium
12 Posts
Posted - 11/08/2012 :  09:51:53  Show Profile  Reply with Quote
I don't understand what represent t1.Account and t3.directions

Maybe 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 above

My table name is tblFactMGMTAPP.

Thanks
Edited by - fholemans on 11/08/2012 09:53:40
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2234 Posts
Posted - 11/09/2012 :  00:14:16  Show Profile  Reply with Quote 

SELECT t1.*, t2.Directions
FROM (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
      ) t1
JOIN (SELECT top 1 Category, M_account, Directions FROM tblFactMGMTAPP) t2
ON t1.category = t2.category AND t1.m_account = t2.m_account


--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000