When use where <> 100 not get value not equal 100?

Transact-SQL
,
1

I work on SQL server 2012 . i face issue where <> 100 not working and display 100
why

select f.chemicalid, sum(isnull(SubstanceComponentLevelPercentage,0)) as TotalSumPerChemical into #gettotalsubstance from  Parts.ChemicalProfiles f WITH(NOLOCK)
 where  SubstanceComponentLevelPercentage  <> 100 
 group by  f.chemicalid

SubstanceComponentLevelPercentage is float

the following result of my query
why Total display 100 this what ask about it
are there are any wrong on query

chemicalid TotalSumPerChemical
348202 99.9999560885723
495504 100
194580 99.999957656118
568923 100
578200 100
547901 100
666106 100
161241 100
334800 99.999957922015
512484 100
592687 100
3

hi ahmed

what happenned !!! again no response .. looks like you are busy !!

4

100 can come in different scenarios

image
image1692×596 44.1 KB

1 Like
5

The simple answer is that you're using FLOAT, which is NOT based on a DECIMAL floating value. Rather, it's based on a BINARY floating value and then the answer is displayed as a DECIMAL. It makes a huge difference because there are some decimal values that simply cannot be calculated with the limited precision of 15 digits and a scale that decreases by 1 for every digit to the left of the decimal point in the FLOAT datatype..

When using things like Division, it does its best to provide the correct answer but they ARE binary calculations that are NOT based on the decimal values we know an love and so have the "approximation" that I described above. They're not actually approximations... they the "best" answer that you can come up with using BINARY mathematical operations with limited precision.

With that being said, if you want to compare for 100, you're obviously going to have to correctly round the answer of your FLOAT-based calculations as the following example portrays...

DECLARE @SomeFloat FLOAT = 99.999957922015
;
 SELECT  OriginalFloat = @SomeFloat
        ,RoundedFloat  = ROUND(@SomeFloat,4)
;

Results:
image

2 Likes
6

@harishgg1... Just as a matter of posting style and a friendly tip, my recommendation is that you stop using !!! in your replies because, just like typing in all caps, a lot of people interpret !!! as you yelling.

7

Thanks Jeff

Sure .. Thing .. i have modified my post to remove it ..

8

I guess its Etiquette .. Polite Manners at the dining table .. Chewing without making noise

Thanks Jeff ..

1 Like
9

thank you very much

10

Thank you for taking my suggestion the right way, @harishgg1.

1 Like
11

Jeff

Lot of times .. we don't see what the other person ( or person's ) are seeing

But have to give respect and oblige ... Part of being human

1 Like
12

Totally agreed. I used to be an electronics instructor for the U.S. Navy. We taught sailors from many different countries and there were a whole lot of rules to follow to avoid offending folks. And... as you may have guessed, there's a whole lot of things that offend folks.

I wasn't offended by your use of !!!. I just wanted you to realize that a lot of people would consider that as yelling.

1 Like
13

Thanks Jeff

Been in that boat many many times .. Fortunately no body has kicked my a.... :smile:

14

I believe it's because your where is on components and your sum is on the Total, as [harishgg1] noted.