CODEWITHSUNDEEP
mysqlsql
GeelZyen
GeelZyen

Reputation: 11

Why this sql code throws error

I have an SQL code for calculating rank on the leaderboard from won matches and the played matches:

SELECT count(1) 
FROM `account` 
WHERE points < SELECT points FROM (SELECT `display name`, 3*`wins`-`matches played` AS points FROM `account`) AS T WHERE `display name` = "valaki"

The left part of the inequality is working if I change the points for wins or any existing field, the left part of it works as well, but the code doesn't works.

Upvotes: 0

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You need parentheses around subqueries:

SELECT count(1)
FROM account a
WHERE a.points < (SELECT 3*a2.wins-a2.matchesplayed AS points
                  FROM account a2
                  WHERE a2.displayname = 'valaki'
                 );

In addition, you are referring to columns as matches played and diplay name. That is not correct. You need escape characters around column names with spaces. My advice is to fix the column names, so they don't need to be escaped.

I also added table aliases. These are a good habit. They really should be mandatory if a query references more than one table or has more than one FROM clause.

Upvotes: 1

Related Questions