How to compare values in MySql table as a percent?

[flynismo]

Hi all,

basically, what I am having trouble with is this:

I have a value stored in a table for each member, let's call it $my_value. Each member has a $my_value, which is an integer.

What I want to do is as follows...
Lets assume I log in as user A. I would like to list all the member's in the database where the other users' $my_value is no more than 5% larger of user A's $my_value and no more than 5% lower than user A's $my_value.

Any assistance would be very much appreciated!

[anilashanbhag]

select all values in table for which this fieldvalue lies between givenvalue*0.95 and givenvalue*1.05 wont that work ?

[flynismo]

My apologies, I just realized that I didn't explain it correctly.

I can get the correct range of $my_value to populate the query, but what I need to do is display the result as a percentage.

For example, User A has a $my_value of 100. User B has a $my_value of 97.
So, if User A performs a search, he will find User B, since User B falls within the +/- 5% range.
But rather than displaying User B's $my_value, I want to convert his $my_value into a percentage when compared to User A.

In other words:

1. User A ($my_value = 100) performs search and finds User B ($my_value = 97).

2. User A sees User B as 97% (since 97 is 97% of User A's 100)

.... or ....

3. User C ($my_value = 19) finds User D ($my_value = 20)

4. User D's $my_value of 20 is shown as 95% (since 19 is 95% of 20)

[Akash Saikia]

Hi try this one... :)

select * from table
where myvalue >= ((select myvalue from table where name='A') - (select myvalue from table where name='A') * 5/100 )
and myvalue <= ((select myvalue from table where name='A') + (select myvalue from table where name='A') * 5/100 )

It will also show the records including A's myvalue,if you don't want it to be listed then put one more where condition at the end. :D

I hope it helps...

It it helps,mark it as solved so it can help others...

[flynismo]

That looks like it should work, I will give it a try!