Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-28-2015
10:26 PM

01-28-2015
10:26 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-28-2015
10:54 PM

01-28-2015
10:54 PM

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')

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?**

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-31-2015
08:07 AM

01-31-2015
08:07 AM

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))