Search code examples
mysql

MySQL query returning unexpected result for counting query

I have a MySQL table named members with the following structure and sample data:

id memberOneId memberTwoId memberOneScore memberTwoScore
1 1002 1001 100 90
2 1002 1001 95 65
3 1001 1002 55 60

My exmple user has ID 1001. I want to get count of his victories with scores he have with follwing query:

select count(*) as aggregate 
from `members` 
where (`memberOneId` = 1001 and `memberOneScore` > `memberTwoScore`)         
   or (`memberTwoId` = 1001 and `memberTwoScore` > `memberOneScore`)

I expect this query to return 0, as the user with ID 1001 always has a lower score than their opponent in all rows.

However, the query is returning 1 as the result.

Can anyone help me understand why this query is returning an unexpected result and suggest how to fix it?

I always get unexpected result.

Solution

  • I don't know what is your scores column datatype. If it's integer then your query will work properly. Otherwise do type cast and updated query is:

    select count(*) as aggregate 
from `members` 
where (`memberOneId` = 1001 and cast(`memberOneScore` as signed int) > cast(`memberTwoScore` as signed int)) 
or    (`memberTwoId` = 1001 and cast(`memberTwoScore` as signed int) > cast(`memberOneScore` as signed int))

    Sample code: db<>fiddle

    Thank you