October 4, 2007 at 11:09 am
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?
October 4, 2007 at 11:48 am
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
October 4, 2007 at 11:48 am
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.
October 4, 2007 at 1:36 pm
SUM(F1 - ROUND( F1, 0, 1) worked great. Thank you.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply