SQL grouping sets and roundup

Question

I am trying to calculate the difference between a certain sum of values and the same sum using specific roundup rules (columns 5 and 6):

select
    A,
    B,
    C,
    sum(D),
    sum(D)/300,
    case when sum(D)/300 < 1.5 then 0 else round(sum(D/300), 0) end

from table

group by grouping sets ((A,B,C), ())

The SQL works, but the final row is wrong. The totals in the final row seem correct for column 4 and 5, but in column 6 is doesn't add up the rounded up values of the column, but the rounded up value of the total of column 5...

What am I doing wrong? (Goal: compare the totals of column 5 and 6)

Any help is welcome!

EDIT:

the result right now is something like this (only column 5 and 6):

1,2 0
1,5 2
3,1 3

5,8 6

The total of the second column should say 5 in this example, but it says 6, using the unrounded values...

If I understand your question, you may be under the impression that the "grand total" for column 6 is calculated from the partial totals for column 6. ("Total" in this context meaning divided by 300 and then rounded unless less than 1.5.) It isn't - the `sum(D)` used in the column 6 computation for the grand total row is still based on the individual values of D in each row, not on the rounded totals for the partial sums. — , Jul 12 '17 at 20:50
Yes! That is what I expected. So how do I get the total of the rounded values? WIll a subquery on this table use the right numbers or also sill use D? — Victor, Jul 13 '17 at 05:27

score 0 · Answer 1 · answered Jul 12 '17 at 19:40

0

You are missing the outer SUM:

select
    A,
    B,
    C,
    sum(D),
    sum(D)/300,
    SUM(case when sum(D)/300 < 1.5 then 0 else round(sum(D/300), 0) end) as result

from table

group by grouping sets ((A,B,C), ())

answered Jul 12 '17 at 19:40

LONG

4,490
2
17
35

I tried it, but I get the following Error: ORA-00979: not a GROUP BY expression – Victor Jul 12 '17 at 20:00

SQL grouping sets and roundup

1 Answers1