CODEWITHSUNDEEP
mysqlsql
Joe Huang
Joe Huang

Reputation: 6570

SQL query by adding two columns in where clause?

I have a database table that contains two scores:

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.

Upvotes: 2

Views: 14985

Answers (7)

StuartLC
StuartLC

Reputation: 107277

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

Upvotes: 7

kamalakar
kamalakar

Reputation: 111

now it's possible to add condition with column names in where clause like this

select * from hotel_rooms where (total_pax + extra_beds >= 3);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270061

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.

Upvotes: 7

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

You might be looking for HAVING clause

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

Upvotes: 1

Alexander
Alexander

Reputation: 3179

You can use HAVING clause for this.

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

From documentation: "The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well."

Upvotes: 1

scraatz
scraatz

Reputation: 419

You cannot use the calculated fields name in where and order by but you can use the formula:

SELECT scorea + scoreb FROM data WHERE scorea+scoreb > 100 order BY scorea+scoreb

You also could create a view with the field scorec.

Upvotes: 2

Simone Nigro
Simone Nigro

Reputation: 4887

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

Upvotes: 1

Related Questions