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 |