asked
Please Help with Subquery
I have a table T with four dimensions and one measure like this: Dim1, Dim2, Dim3, Dim4, Msr 1A, 2A, 3A, 4A, 100 1A, 2A, 3A, 4B, 50 1A, 2A, 3B, 4A, 75 1A, 2A, 3B, 4B, 25 I would like to inject a new measure (Msr_new) that sums (Msr) ONLY FOR unique combinations of Dim1 through 3 (not Dim4), such that result set like Dim1, Dim2, Dim3, Dim4, Msr, Msr_new 1A, 2A, 3A, 4A, 100, 150 1A, 2A, 3A, 4B, 50, 150 1A, 2A, 3B, 4A, 75, 100 1A, 2A, 3B, 4B, 25, 100 I tried SELECT Dim1...Msr, SELECT Sum(Msr) FROM T AS SubT GROUP BY SubT.Dim1,...SutT.Dim3 with no luck. Any help much appreciated.
Try this ... declare @YourTable table ( Dim1 varchar(5), Dim2 varchar(5), Dim3 varchar(5), Dim4 varchar(5), Msr int) insert into @YourTable select '1A', '2A', '3A', '4A', 100 insert into @YourTable select '1A', '2A', '3A', '4B', 50 insert into @YourTable select '1A', '2A', '3B', '4A', 75 insert into @YourTable select '1A', '2A', '3B', '4B', 25 select Dim1 , Dim2 , Dim3 , Dim4 , Msr, sum(Msr)over(partition by Dim1, Dim2, Dim3) as Msr_new from @YourTable Dim1 Dim2 Dim3 Dim4 Msr Msr_new ----- ----- ----- ----- ----------- ----------- 1A 2A 3A 4A 100 150 1A 2A 3A 4B 50 150 1A 2A 3B 4A 75 100 1A 2A 3B 4B 25 100 (4 row(s) affected)
17 People are following this question.
