question

jms19 avatar image
jms19 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.
subquery
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
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)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.