CODEWITHSUNDEEP
mysql
Rikard
Rikard

Reputation: 7805

Add the values from multiple columns, and compare it in the where clause

I made a SELECT to add different values from a row into a maxguests field.

What I do not manage is to re-use that virtual value in the WHERE clause.

My query is 

SELECT 
    id,
    single_beds + single_bed_sofas + double_beds * 2 + double_bed_sofas * 2 AS maxguests
FROM items

and this works good, as expected.

Now I would like to add WHERE maxguests > 5 but I get error that the colums is unknown.

Error Code: 1054. Unknown column 'maxguests' in 'where clause'

Is aliasing that sum into maxguests not enough to be able to use it in the query?

Upvotes: 0

Views: 91

Answers (2)

Darshan Mehta
Darshan Mehta

Reputation: 30829

You can use HAVING clause to compare the virtual column value, e.g.:

SELECT 
    id,
    single_beds + single_bed_sofas + double_beds * 2 + double_bed_sofas * 2 AS maxguests
FROM items
HAVING maxguests > 10;

Here is a example in SQL Fiddle.

Upvotes: 1

smozgur
smozgur

Reputation: 1812

Try HAVING.

SELECT 
id,
single_beds + single_bed_sofas + double_beds * 2 + double_bed_sofas * 2 AS maxguests
FROM items
HAVING maxguests > 5

Upvotes: 1

Related Questions