Round function with Rollup - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Round function with Rollup

Author

Topic

sqlserverlearner
Starting Member

21 Posts

Posted - 2008-12-31 : 12:06:56

Im using Round() function in the query to round data > .5 to 1 and .<5 = 0 but when used with ROLLUP then individual rows are correct data but the grand total is not giving correct value. Can anyone please help me to resolve this. My sample query is like this

SELECT
CASE
WHEN Grouping(fieldgrp) = 1
THEN 'Grand Total'
ELSE
fieldgrp
END
, ROUND(SUM(CASE
WHEN ((tabA.Month BETWEEN @MinMonth AND @MaxMonth) AND (tabB.Mnth = 'AUG'))
THEN tabA.Hours
ELSE
0
END),0) AS AUGUST,
ROUND(SUM(CASE
WHEN ((tabA.Month BETWEEN @MinMonth AND @MaxMonth) AND (tabB.Mnth = 'SEP'))
THEN tabA.Hours
ELSE
0
END),0) AS SEPTEMBER,
ROUND(SUM(
(CASE WHEN ((tabA.Month BETWEEN @MinMonth AND @MaxMonth) AND (tabB.Mnth = 'JAN')) THEN Round(tabA.[Hours],0) ELSE 0 END)
+ (CASE WHEN ((tabA.Month BETWEEN @MinMonth AND @MaxMonth) AND (tabB.Mnth = 'FEB')) THEN Round(tabA.[Hours],0) ELSE 0 END)
),0) AS FYTotal

FROM tabA
INNER JOIN tabB on .........

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-12-31 : 12:50:09

This seems to work for me. but round(n,0) rounds >= .5 up not down.


select case when grouping(grp) = 1 then 'total' else grp end as [grouping]
       ,sum(val) total
       ,round(sum(val),0) total_with_round
from   (
       select 'jan' [grp], 1.1 [val] union all
       select 'jan', 2.3 union all
       select 'jan', 1.4 union all
       select 'feb', 1.8 union all
       select 'feb', 2.8 union all
       select 'feb', 1.9 
       ) d
group by grp
with rollup

output:
grouping total                                   total_with_round
-------- --------------------------------------- -----------------
feb      6.5                                     7.0
jan      4.8                                     5.0
total    11.3                                    11.0

EDIT:
Or is the problem that you want the [total_with_round] [total] to sum the rounded values rather than the raw values?

Be One with the Optimizer
TG

sqlserverlearner
Starting Member

21 Posts

Posted - 2008-12-31 : 13:00:25

I have used Round() in the sample query I have posted but still the Total was showing differnt value because it did not rounded the value and did sum.

sqlserverlearner
Starting Member

21 Posts

Posted - 2008-12-31 : 13:03:02

Thank You guys for whoever noticed this posting. I did add a ROUND() function and it worked as expected. Thanks for all Folks again. Happy NEW YEAR to everyone in the forum.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources