Round with SUM - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Round with SUM

Author

Topic

mikemcg36
Starting Member

9 Posts

Posted - 2011-11-02 : 10:18:19

I have the following query and want to round the sums to 2 decimal places:


SELECT
	table1.customer,
	ROUND (SUM(case when table2.brand = '1' then (table3.Qty * table4.Milliliters) / 100000 else 0 end),2) AS "coke",
	SUM(case when table2.brand = '2' then (table3.Qty * table4.Milliliters) / 100000 else 0 end) AS "pepsi",
FROM
	[TBS_Files].[dbo].[table1],
	[TBS_Files].[dbo].[table2],
	[TBS_Files].[dbo].[table3],
	[TBS_Files].[dbo].[table4],
	[TBS_Files].[dbo].[table5]
WHERE
	table3.id = table5.id and
	table5.Cust_no = table1.Cust_no and
	table3.brand = table2.brand and
	table4.Size = table3.Size
GROUP BY
	table2.brand,
	customer,
	Qty,
	Milliliters

How can I round the SUMs I am SELECTing to 2 decimal places??

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-02 : 10:20:35

Doesn't what you have work?
You might be doing integer arithmetic depending on the datatypes - maybe

ROUND (SUM(case when table2.brand = '1' then (1.0 * table3.Qty * table4.Milliliters) / 100000 else 0 end),2) AS "coke",

and the group by should probably be cutomer only.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mikemcg36
Starting Member

9 Posts

Posted - 2011-11-02 : 10:31:54

The query runs but it does not round my number. I have about 10 decimal places.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 10:40:12

whats the datatype of fields? anyways casting it to decimal(15,2) will reduce it to 2 decimal places

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-02 : 10:40:36

Add in a convert(decimal(18,2),.........)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mikemcg36
Starting Member

9 Posts

Posted - 2011-11-02 : 10:45:10

Convert(decimal(18,2),....

Worked! Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 10:45:55

quote:
Originally posted by mikemcg36

Convert(decimal(18,2),....

Worked! Thanks!

i think reason was your original field had scale value greater than 2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources