How to refer to a column alias in WHERE clause

Question

I want to compare an alias (which has white spaces in it) with a white space in WHERE clause.

select r.resortid, sum(b.adultcount+b.childcount) as "Total Guest"
from   resort r, booking b
where  r.resortid = b.resortid and "Total Guest" <= 10
group by r.resortid
order by r.resortid;

The error I get is:

where  r.resortid=b.resortid and "Total Guest" <=10
                                 *
ERROR at line 3:
ORA-00904: "Total Guest": invalid identifier

score 3 · Answer 1 · answered May 02 '20 at 11:21

Use HAVING Clause & use explicit standard JOIN syntax instead of comma separate :

SELECT r.resortid, SUM(b.adultcount+b.childcount)
FROM resort r INNER JOIN
     booking b
     ON r.resortid = b.resortid 
GROUP BY r.resortid
HAVING SUM(b.adultcount+b.childcount) <= 10;

Where clause filter only raw data not after aggregation of data.

score 2 · Answer 2 · answered May 02 '20 at 12:01

Given that a resort could have no bookings, I would recommend a left join:

select r.resortid, coalesce(sum(b.adultcount + b.childcount), 0) as total_guests
from resort r join
     booking b 
     on r.resortid = b.resortid 
group by r.resortid
having coalesce(sum(b.adultcount + b.childcount), 0) <= 10
order by r.resortid;

If all resorts have bookings, then the join is not necessary:

select b.resortid, sum(b.adultcount + b.childcount) as total_guests
from booking b 
group by b.resortid
having sum(b.adultcount + b.childcount) <= 10
order by r.resortid;

ulisse009 · Answer 3 · 2020-05-02T14:42:50.503

You can't refer to an alias in the condition defined in the same sql statement.

You have 3 possibility in Oracle/MariaDB/MySQL databases:

1) Rewrite calculated column and, if it's calculated by aggregation function, you have to put the condition in "HAVING" clause:

SELECT r.resortid, sum(b.adultcount+b.childcount) as "Total Guest"
  FROM resort r, booking b
 WHERE r.resortid = b.resortid
 GROUP BY r.resortid
  HAVING sum(b.adultcount+b.childcount) <= 10
ORDER BY r.resortid;

2) Using subquery:

SELECT *
  FROM
    (SELECT r.resortid, sum(b.adultcount+b.childcount) as TotalGuest
      FROM resort r, booking b
     WHERE r.resortid = b.resortid
     GROUP BY r.resortid) AS totalg
 WHERE TotalGuest <= 10
ORDER BY resortid;

3) Write subquery using "WITH" clause:

WITH totalg AS
(SELECT r.resortid, sum(b.adultcount+b.childcount) as TotalGuest
  FROM resort r, booking b
 WHERE r.resortid = b.resortid
GROUP BY r.resortid)
SELECT *
  FROM totalg
 WHERE TotalGuest <= 10
ORDER BY resortid;

score 0 · Accepted Answer · answered May 02 '20 at 11:22

Having, not where; also, JOIN wouldn't do any harm.

select r.resortid, 
       sum(b.adultcount + b.childcount) as "Total Guest"
from resort r join booking b on r.resortid = b.resortid 
group by r.resortid
having sum(b.adultcount + b.childcount) <= 10
order by r.resortid;

How to refer to a column alias in WHERE clause

4 Answers4