sum() in group in sql reporting services

I create two group in a report, for example in group1(ID) 110116, the data
items are like the following:

110116 (group1)

TC0012
      1
      1               (1)

TC0011
      1.5
      1.5      (1.5)

TC0010
      1.5
      1.5            (1.2)

I need a sum 1 + 1.5 + 1.5 = 4 , but I always get 8 when I use sum(fields,
scope) in reporting services.

Is there any way to implement it ? Appreciate for any comments and
suggestions.

Lisa
lisayanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lilian-arnaudCommented:
SQL computes the sum on all the rows returned by your query :

Column1;Column2;Column3
-------------------------------
110116;TC0012;1;null
110116;TC0012;1;(1)
110116;TC0011;1.5;null
110116;TC0011;1.5;(1.5)
110116;TC0010;1.5;null
110116;TC0010;1.5;(1.2)
------------------------------
Sum(Column2)   1+1+1.5+1.5+1.5+1.5=8

If you want 1+1.5+1.5, you need filter your tuples returned ... you can try to sum a formula like this (for Oracle):

Select Column1,sum(Decode(Column3,null,Column2,0))
...




0
ParsiCommented:
Hi Lisa
 
I am not sure if I.ve got your question correctly.
Seems that u need to select one symbol (such as maximum) of each group
and calculate the sum.
if so this shall help u.

Select Sum(X) from
(Select Id, Max(Col1) X
from Table
Where Condition
Group by Id)

Good luck.
Parsi
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lisayanAuthor Commented:
Hi guys,

I have resolved my problem. Although the method is not the same as yours, I still give points to you two.  My solution is:  

1. use select sum(column) as counter from ....    get counter number for each row.

2. use sum(fields!column_value/field!counter)    get all sum()


Lisa
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.