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.
Advertisement
Answer
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.