Decimal issue

Database
Enthusiast

Decimal issue

Hi there, 

I have 2 querys and the result is a little different with the no of digits after decimal. For this field(data_month_1) it is defined as decimal(22,7) in the data type for both the tables.

Basicall we do a load of 90% of data_volume_month_01 from table 1 to table 2 and hence the values should match. Can anybody help whyc it shows different value after the decimal digits?

select (sum(data_volume_month_01)*0.9)

from 

table 1

where sales_force='MUP' and state='DA' and data_month='201405'

Result: 1,231.78217697

select sum(data_volume_month_01)

from 

table 2

where sales_force='MUP'


Result:  1,231.7821777

2 REPLIES
Enthusiast

Re: Decimal issue

To put it a little more simpler, below is the query.

select sum(data_volume_month_01)  from 

table1

where sales_force='MUP' and state='CA' and data_month='201411'  and outlet_code in('91745553')

   RESULT :   -3.0303030

so 90% of the above result should be   -2.7272727 available in table 2 which am getting .

select sum(data_volume_month_01)

from 

TABLE2

where sales_force='MUP' and state='CA' and data_month='201411'  and outlet_code in('91745553')

  RESULT :-2.7272727

Now to show the comparison between both the tables what should be multiplied in query 1 so that i get the same result as that of in Table 2?

Senior Apprentice

Re: Decimal issue

When you divide by 0.9 you increase the number of fractional digits to 8.

Simply cast back to 7 digits using

cast(sum(data_volume_month_01)*0.9 as dec(22,7))