Select Decimal

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Select Decimal

Select Decimal

Rate Topic

Display Mode

Topic Options

Author

Message

azzamein

Posted Thursday, October 04, 2007 11:09 AM

Forum 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

Andre St-Onge

Posted Thursday, October 04, 2007 11:48 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 09, 2007 12:26 PM
Points: 7, Visits: 15

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

David Webb-CDS

Posted Thursday, October 04, 2007 11:48 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:29 PM
Points: 827, Visits: 5,713

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

azzamein

Posted Thursday, October 04, 2007 1:36 PM

Forum 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 »

Permissions