Prev: Need advice with ISNULL
Next: OPENQUERY
From: Luigi on 21 Apr 2010 14:37
Hi all
I can't understand why SQL Server numeric data truncate at the sixth decimal
place, when the decimal places should be much more.
Here is the example.
I have these 3 tables with test data:

create table Consumi1 (Combustibile varchar(10), Valore decimal(30,19))
go
create table Consumi2 (Combustibile varchar(10), Valore decimal(30,19))
go
create table Consumi3 (Combustibile varchar(10), Valore decimal(30,19))
go
insert Consumi1 values ('Carbone', 123.4567898765434),
('OCD',456.574747473737), ('Gasolio',9987.8473736666)
insert Consumi1 values ('Carbone', 523.5499292929292),
('OCD',868.183837474744), ('Gasolio',45.57575757388)
go
insert Consumi2 values ('Carbone', 123.4567898765434),
('OCD',456.574747473737), ('Gasolio',9987.8473736666)
insert Consumi2 values ('Carbone', 123.3434343434343),
('OCD',436.443243434343), ('Gasolio',97.12121212121)
go
insert Consumi3 values ('Carbone', 123.4567898765434),
('OCD',456.574747473737), ('Gasolio',9987.8473736666)
insert Consumi3 values ('Carbone', 56.5638383838383),
('OCD',45.583838383827), ('Gasolio',995.438280282828)


The numbers are completely random, but only serve to put an abundant number
of decimal digits right to the comma.

If I make the following aggregate query:


select a.combustibile, SUM(a.valore)
from
(
select Combustibile, SUM(valore) valore
from consumi1
group by combustibile) a
inner join (
select Combustibile, SUM(valore) valore
from consumi2
group by combustibile ) b
on a.combustibile = b.Combustibile
group by a.Combustibile

I get these results:

combustibile (No column name)
Carbone 647.0067191694726000000
Gasolio 10033.4231312404800000000
OCD 1324.7585849484810000000


that, as you can see, have well over 6 decimals (and so I goes well), while
if I operations to multiplication and Division, as in the following query:


select a.combustibile,
(SUM(a.valore)*SUM(b.valore))/SUM(b.valore)
from
(
select Combustibile, SUM(valore) valore
from consumi1
group by combustibile) a
inner join (
select Combustibile, SUM(valore) valore
from consumi2
group by combustibile ) b
on a.combustibile = b.Combustibile
group by a.Combustibile


I get values truncated at the sixth decimal:

combustibile (No column name)
Carbone 647.006719
Gasolio 10033.423131
OCD 1324.758584

How come this strange behaviour?

Thank you

Luigi

From: Plamen Ratchev on 21 Apr 2010 21:54
Take a look at the following BOL topic which explains the rules for scale calculation on multiplication and division:
http://msdn.microsoft.com/en-us/library/ms190476.aspx?ppud=4

--
Plamen Ratchev
http://www.SQLStudio.com
From: Luigi on 22 Apr 2010 05:17
"Plamen Ratchev" wrote:

> Take a look at the following BOL topic which explains the rules for scale calculation on multiplication and division:
> http://msdn.microsoft.com/en-us/library/ms190476.aspx?ppud=4

And in this case, how can I solve?

Luigi
From: Gert-Jan Strik on 22 Apr 2010 06:03
Luigi wrote:
>
> "Plamen Ratchev" wrote:
>
> > Take a look at the following BOL topic which explains the rules for scale calculation on multiplication and division:
> > http://msdn.microsoft.com/en-us/library/ms190476.aspx?ppud=4
>
> And in this case, how can I solve?
>
> Luigi

In general: make the definition of your variables more tight. You could
explicitely cast them something like decimal(17,13) before doing any
multiplication or division. Or possible change the column definition to
this.

If you change your SELECT statement to a SELECT ... INTO my_table
statement, then you can use sp_help my_table to discover the resulting
decimal definition after the multiplication/division.

--
Gert-Jan
 | 
Pages: 1
Prev: Need advice with ISNULL
Next: OPENQUERY