February 25, 2012 at 1:37 pm
Hello, I have a data set something like (Ignore dashed please just used to try and align things)
Age------D1-----D2----Lv----Num
16-18----All_L---L-----L1----22
16-18----All_L---L-----L2----37
16-18----All_S---S----L1----14
16-18----All_S---VS---L1----12
19+------All_L---L-----L1----12
19+------All_L---L-----L2----12
19+------All_S---S----L1----12
19+------All_S---VS---L1----17
I want to summaries the data so that all the last column is summarised grouped by the following
Age
Age and D1
Age, D1 and D2
Is this possible to complete with the same query? My final result would be something like
Age-----D1----D2---Lv----Num--Ga---GaD1---GaD1D2
16-18---All_L--L----L1----22----85----59------59
16-18---All_L--L----L2----37----85----59------59
16-18---All_S--S---L1----14----85----26------14
16-18---All_S--VS--L1----12----85----26------12
19+-----All_L---L----L1----12----53----24------24
19+----All_L---L----L2----12----53----24------24
19+----All_S--S----L1----12----53----29------12
19+----All_S--VS---L1----17----53----29-----17
Sorry if the numbers do not quite add up. Its important that the results are in the same table. Also the result set need to be dynamic. For example if a filter was applied and the 3rd row was removed the total Ga, GaD1 and GaD1D2 need to reflect this.
Any ideas.
Thanks
February 25, 2012 at 1:41 pm
Did you try using SUM() OVER()?
SUM(Num) OVER (PARTITION BY Age) AS Ga
SUM(Num) OVER (PARTITION BY Age, D1) AS GaD1
SUM(Num) OVER (PARTITION BY Age, D1, D2) AS GaD1D2
February 26, 2012 at 1:59 am
LutzM (2/25/2012)
Did you try using SUM() OVER()?SUM(Num) OVER (PARTITION BY Age) AS Ga
SUM(Num) OVER (PARTITION BY Age, D1) AS GaD1
SUM(Num) OVER (PARTITION BY Age, D1, D2) AS GaD1D2
This works brilliant, thanks for the replies!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply