0

This is my query:

SELECT c.remaining_budget, 
       c.future_liabilities, 
       c.used_this_month, 
       bi.full_name, 
       bi.id                                                             AS 
       item_id, 
       (SELECT Sum(amount) 
        FROM   balance_histories 
        WHERE  balance_histories.budget_item_id = 21 
               AND Date_format(payment_date, '%Y-%m-01') = '2018-01-01') AS 
       monthly_usage 
FROM   families_budget_items_calcs c 
       LEFT JOIN budget_items bi 
              ON c.budget_item_id = bi.id 
WHERE  c.family_id = 54824 
       AND bi.id IN ( 21 ) 
       AND monthly_usage <> 0

When I execute this query I get an error: unknown column 'monthly_usage' in WHERE clause.

Why? It's there!

Improve this question

2 Answers 2

Reset to default
2

The problem is being caused by that the alias monthly_usage you defined in your select clause is not available when the WHERE clause gets evaluated. One immediate workaround here would be to move the check on the monthly_usage to a HAVING clause:

WHERE
    c.family_id = 54824 AND
    bi.id IN ( 21 )
HAVING
    monthly_usage <> 0

If you don't want to do this, then you may simply repeat the subquery for monthly usage in the WHERE clause.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Why wouldn't I want to do this?
Well, only MySQL has overloaded HAVING to behave this way. If you went with the HAVING option, and then tried to port the query to another database, e.g. SQL Server or Oracle, then it would likely not run. But repeating the subquery in the WHERE clause should run everywhere.
"the alias monthly_usage you defined in your select clause is not available when the WHERE clause gets evaluated". Why?
I'm not an expert in this, but I believe it has to do with that the WHERE clause runs first to filter off records not relevant to the query. This happens before the select clause assigns aliases. It is an order of operations issue.
Noam, We are talking about something called logical query processing, this means that in your query FROM statement is evaluated and processed initially and then the rest of query. In LQP terms queries will be processed in the following order. { FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> ORDER BY } So, aliasing in the select clause is used only for more relational result, so your columns can have more suitable names.
1

Tim has the correct answer (and I've upvoted it). But your query has other issues. It would be better written as:

SELECT c.remaining_budget, c.future_liabilities, c.used_this_month, 
       bi.full_name, bi.id as item_id,
       (SELECT Sum(bh.amount) 
        FROM balance_histories bh
        WHERE bh.budget_item_id = 21 AND
              payment_date >= '2018-01-01' AND
              payment_date < '2018-02-01'
       ) as monthly_usage 
FROM families_budget_items_calcs c INNER JOIN
     budget_items bi 
     ON c.budget_item_id = bi.id 
WHERE c.family_id = 54824 AND
      bi.id IN ( 21 ) 
HAVING monthly_usage <> 0 ;

Notes:

  • Your WHERE clause turns the outer join to an inner join, so specifying LEFT JOIN is misleading.
  • In a query with more than one table reference, you should qualify all column names (unless your joins use USING).
  • The date comparison should not use a function such as date_format(). First it is hard to follow the logic. Second, it prevents the use of an index.
Improve this answer

1 Comment

Sometime answering the question isn't enough +1.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.