ROLLUP and summarazing values

7 Apr 2006 9:48 AM

simonZ

I have query:

SELECT column4,column5,sum(column1)as sum1,sum(column2) as sum2,sum(column3)
as sum3,
result=case when sum(column1)<(sum(column2)+sum(column3)) then 0 else
sum(column1)-sum(column2)-sum(column3) end
FROM testTable GROUP BY column4,column5 WITH ROLLUP HAVING column4 is not
null

the result is for example:

column4        column5        sum1        sum2        sum3        result
    1                    1              100           60            70
0
    1                    2              200           30            10
160
    1                    3              100           20            50
30
    1                    4              300           70            80
150
    1                    5              400           90            70
240
    1                   null           1100        270           280
550

The question is: Why is the result 550 instead of 580 and how can I get the
correct result?
It is obvious that it takes result:-30 for first row insead of 0 into its
summarazing rows.

I think this example is intelligible and simple enough, if not, I will
create and post some DDL and test data.

Regards,S

7 Apr 2006 11:59 AM

Omnibuzz

Hi SimonZ,
The result for the rollup is evaluated based on the expression you had
given

result=case when sum(column1)<(sum(column2)+sum(column3)) then 0 else
sum(column1)-sum(column2)-sum(column3) end

and that is evaluated even for the rolled up row

7 Apr 2006 12:02 PM

David Browne

Show quote Hide quote

"simonZ" <simon.zu***@studio-moderna.com> wrote in message
news:%23omEBiiWGHA.4652@TK2MSFTNGP04.phx.gbl...
>I have query:
>
> SELECT column4,column5,sum(column1)as sum1,sum(column2) as
> sum2,sum(column3) as sum3,
> result=case when sum(column1)<(sum(column2)+sum(column3)) then 0 else
> sum(column1)-sum(column2)-sum(column3) end
> FROM testTable GROUP BY column4,column5 WITH ROLLUP HAVING column4 is not
> null
>
> the result is for example:
>
>
> column4        column5        sum1        sum2        sum3        result
>    1                    1              100           60            70 0
>    1                    2              200           30            10 160
>    1                    3              100           20            50 30
>    1                    4              300           70            80 150
>    1                    5              400           90            70 240
>    1                   null           1100        270           280 550
>
> The question is: Why is the result 550 instead of 580 and how can I get
> the correct result?
> It is obvious that it takes result:-30 for first row insead of 0 into its
> summarazing rows.
>
> I think this example is intelligible and simple enough, if not, I will
> create and post some DDL and test data.
>

Because of the case statement in your "result" expression, that expression
is not a pure additive measure. IE
result(A+B) <> result(A) + result(B). In the case of (column4,column5) =
(1,1) the negative result gets netted into the grand totals, but ignored in
the individual totals, the result expression applied to the grand totals
doesnt match the sum of the individual results.

David