Asked  6 Months ago    Answers:  5   Viewed   36 times

I need to use an alias in the WHERE clause, but It keeps telling me that its an unknown column. Is there any way to get around this issue? I need to select records that have a rating higher than x. Rating is calculated as the following alias:

sum(reviews.rev_rating)/count(reviews.rev_id) as avg_rating



You could use a HAVING clause, which can see the aliases, e.g.

 HAVING avg_rating>5

but in a where clause you'll need to repeat your expression, e.g.

 WHERE (sum(reviews.rev_rating)/count(reviews.rev_id))>5

BUT! Not all expressions will be allowed - using an aggregating function like SUM will not work, in which case you'll need to use a HAVING clause.

From the MySQL Manual:

It is not allowable to refer to a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section B.1.5.4, “Problems with Column Aliases”.

Tuesday, June 1, 2021
answered 6 Months ago

Instead of using a subquery, you may get the values from a left join and count the distinct not null values.

SELECT meta_name, meta_value, COUNT(DISTINCT pid) count
  FROM meta m JOIN item_meta im
    ON im.field_id =
    SELECT pid
  FROM item_meta im JOIN items i
    ON im.item_id = JOIN meta m
    ON im.field_id =
HAVING MAX(meta_name = 'Make' AND meta_value = 'BMW') = 1
   AND MAX(meta_name = 'Car Type' AND meta_value = 'Coupe') = 1)
LJ ON im.item_id =
 GROUP BY meta_name, meta_value;
Saturday, May 29, 2021
answered 7 Months ago

This is not possible directly, because chronologically, WHERE happens before SELECT, which always is the last step in the execution chain.

You can do a sub-select and filter on it:

  SELECT A.identifier
    , TO_NUMBER(DECODE( A.month_no
      , 1, 200803 
      , 2, 200804 
      , 3, 200805 
      , 4, 200806 
      , 5, 200807 
      , 6, 200808 
      , 7, 200809 
      , 8, 200810 
      , 9, 200811 
      , 10, 200812 
      , 11, 200701 
      , 12, 200702
      , NULL)) as MONTH_NO
    , TO_NUMBER(TO_CHAR(B.last_update_date, 'YYYYMM')) as UPD_DATE
  FROM table_a A
    , table_b B
  WHERE A.identifier = B.identifier
) AS inner_table

Interesting bit of info moved up from the comments:

There should be no performance hit. Oracle does not need to materialize inner queries before applying outer conditions -- Oracle will consider transforming this query internally and push the predicate down into the inner query and will do so if it is cost effective. – Justin Cave

Saturday, June 5, 2021
answered 6 Months ago

There are two problems with your SQL:

  1. (THis is not the question, but should be considered) by using WHERE over the UNION instead of the tables, you create a performance nightmare: MySQL will create a temporary table containing the UNION, then query it over the WHERE. Using a calculation on a field (LOWER(requester_name)) makes this even worse.

  2. The reason you get two rows is, that UNION DISTINCT will only suppress real duplicates, so the tuple (someuser,peter) and the tuple (someotheruser, peter) will result in duplication.


To make (someuser, peter) a duplicate of (peter, someuser) you could use:

  IF(requester_name='peter', receiver_name, requester_name) AS otheruser
  IF(requester_name='peter', receiver_name, requester_name) AS otheruser

So you only select someuser which you already know : peter

Tuesday, September 21, 2021
answered 2 Months ago

I can't use my alias in the where clause.

1. Is this approach possible?

Sure, put it in a subquery.

SELECT, [stories.title] & " " & [] & " " & [stories.publisher] & " " & [stories.memo] AS allMyText
FROM stories
WHERE ((([allMyText]) Like "*apples*" And ([allMyText]) Not Like "*applesauce*"));

2. Wouldn't the alternative mean that I'd be performing multiple string concatenations on every row iteration?

Yes that is right, the alternative is to repeat the expression. I won't bore you with the code for this alternative.

For your particular query, you can also use this

SELECT, [stories.title] & " " & [] & " " & [stories.publisher] & " " & [stories.memo] AS allMyText
FROM stories
WHERE ([stories.title] Like "*apples*" OR [] Like "*apples*" 
  OR [stories.publisher] Like "*apples*" OR [stories.memo] Like "*apples*")
AND NOT ([stories.title] Like "*applesauce*" OR [] Like "*applesauce*"
  OR [stories.publisher] Like "*applesauce*" OR [stories.memo] Like "*applesauce*")
Thursday, November 18, 2021
answered 2 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :