[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1643
  • Last Modified:

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
0
lisayan
Asked:
lisayan
2 Solutions
 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now