The mysteries of SUM()

Question

Post reply

The mysteries of SUM()

evald

SSCrazy

Points: 2165
More actions
September 13, 2010 at 7:53 am

#227652

Hello,
i'll never thought i need help for the SUM but here i am.
create this simple table with this values
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[valori]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[valori](
[QTA] [float] NULL,
[TIPO_MOV] [float] NULL
) ON [PRIMARY]
END
GO
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (25, 1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.02, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.1, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.15, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.19, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.19, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.2, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.2, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.2, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.28, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.3, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.3, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.3, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.32, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.35, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.4, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.4, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (0.5, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (1, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (2, -1)
INSERT [dbo].[valori] ([QTA], [TIPO_MOV]) VALUES (4.1, -1)
ok, so you have a table called "valori".
Now try to run this queries:
-- Result is -25 (ok)
select sum(valori.QTA) from valori
where tipo_mov = -1
GO
-- Result is (+25) (ok)
select sum(valori.QTA) from valori
where tipo_mov = 1
-- The result of this query must be 0(zero!!! instead i get -1,77635683940025E-15) (not OK WHY??????)
select sum(valori.QTA * tipo_mov) from valori
Could somebody tell me why i dont get 0 but instead i get that crazy value??
very thank you
and best regards.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 1

That is because float is an approximate-valued data type, unlike the decimal data type.

For example, the value of @a in this T-SQL script will never be 1.

declare @a float, @b-2 float

set @a=0

while @a<>1

begin

print @a

set @a=@a+0.1

end

evald SSCrazy Points: 2165 More actions · Answer 2

:w00t:

Thank you,

i never thought the float type could be the issue.