Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select Decimal Expand / Collapse
Author
Message
Posted Thursday, October 04, 2007 11:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 24, 2012 7:56 AM
Points: 2, Visits: 10
On an SQL 2005 Server I have a table column filled with float values. What I want to do is sum up only the decimal portion of each value. For example, for the values:

160.2
100
79.5

The sum would be .7 (.5 + .2)

My problem is that I am told Decimal is not a valid function, and I really don't want to have to loop through each value in code to pull out/add the decimals.

Is there a way to do this in SQL 2005?
Post #406968
Posted Thursday, October 04, 2007 11:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 7:41 AM
Points: 7, Visits: 17
Just use Round function



IF NOT OBJECT_ID('ANDRETEST') IS NULL
DROP TABLE ANDRETEST
GO

CREATE TABLE ANDRETEST
(
F1 FLOAT,
F2 CHAR(10)
)
GO


INSERT INTO ANDRETEST ( F1, F2 ) VALUES ( 160.2,'TEST')
INSERT INTO ANDRETEST ( F1, F2 ) VALUES ( 100,'TEST')
INSERT INTO ANDRETEST ( F1, F2 ) VALUES ( 79.5,'TEST')



SELECT ROUND(SUM(F1 - ROUND( F1, 0, 1)), 1) AS TEST FROM ANDRETEST

IF NOT OBJECT_ID('ANDRETEST') IS NULL
DROP TABLE ANDRETEST
GO
Post #406990
Posted Thursday, October 04, 2007 11:48 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:31 PM
Points: 893, Visits: 6,882
You cound try something like:

create table #junk (
float_to_add float NOT NULL)
go
insert #junk(float_to_add) values (1.1104)
insert #junk(float_to_add) values (1.222)
insert #junk(float_to_add) values (1.32)
insert #junk(float_to_add) values (1.41)
insert #junk(float_to_add) values (1.52)
insert #junk(float_to_add) values (1.66)
insert #junk(float_to_add) values (1.71)
go

select float_to_add from #junk
go
select sum(convert(numeric(13,3),float_to_add) % 1.000) from #junk
go

drop table #junk
go


But float is not an exact datatype, it's an approximation, so you may get some unexpected results because a number like 1.3 might actually come out as 1.29999999999.

If the numbers you're storing need definite precision, float may not be the appropriate data type.




And then again, I might be wrong ...
David Webb
Post #406992
Posted Thursday, October 04, 2007 1:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 24, 2012 7:56 AM
Points: 2, Visits: 10
SUM(F1 - ROUND( F1, 0, 1) worked great. Thank you.
Post #407054
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse