SQL query by adding two columns in where clause?

Question

I have a database table that contains two scores:

• scoreA
• scoreB

I am trying to make a SQL query by adding these two values, such as

SELECT *,(scoreA+scoreB) as scoreC FROM data WHERE scoreC > 100 ORDER BY scoreC DESC

However, it shows an error:

ERROR: Unknown column 'scoreC' in 'where clause'

Is there any way to work around for this?

P.S. the reason I don't add a new column for scoreC is because scoreA/scoreB are updated continuously by other cron jobs and if I want to calculate scoreC, I need to make extra queries/updates for scoreC, which I would like to avoid to save system resources. However, if it is the only way to calculate scoreC by another cron job, I am also ok with it if it's the only solution. Thanks.

Answer 1

MySQL supports a non-standard extension that allows you to use the having clause in this case:

SELECT *, (scoreA+scoreB) as scoreC
FROM data
HAVING scoreC > 100
ORDER BY scoreC DESC;

Let me emphasize that this is MySQL-specific. For a simple expression such as this, I would just put the formula in the where clause. But sometimes the expression can be quite complicated -- say a distance formula or complex case or subquery. In those cases, the having extension is actually useful.

And, you don't need the formula in the order by clause. MySQL allows column aliases there.

Answer 2

In most ANSI compliant RDBMS, you won't be able to use the derived column ScoreC in the where clause. However, you can do this:

SELECT *
FROM 
(
   SELECT *, (scoreA + scoreB) as scoreC 
   FROM data 
) SummedScores
WHERE SummedScores.scoreC > 100 
ORDER BY SummedScores.scoreC DESC;

where SummedScores is a derived table

Fiddle here