CODEWITHSUNDEEP
sqloracle-databasesumgrouping-sets
Victor
Victor

Reputation: 1

SQL grouping sets and roundup

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...

Upvotes: 0

Views: 405

Answers (1)

LONG
LONG

Reputation: 4610

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), ())

Upvotes: 0

Related Questions