Search code examples
mysqlsqlsumsubqueryhaving-clause

SQL Refer to alias in subquery

I want to query a table which looks like this:

customer_id (INT), transaction_date (DATE), income(INT)

I'm trying to create a table which shows the sum of income per distinct customer_id, except for customers which made transactions ONLY in 2014.

My query:

SELECT DISTINCT(customer_id) AS a_customer_id, sum( case when (SELECT YEAR(transaction_date) FROM table__ WHERE customer_id = a_customer_id) != ('2014') then income else 0 end) AS sum_income FROM table__ GROUP BY a_customer_id ORDER BY sum_income DESC;

The error I receive is "Unknown column a_customer_id". How do I refer a subquery to an alias created in the first query?

Solution

  • Your query is not valid SQL. Based on the description of your question, you seem to want aggregation and a having clause:

    select customer_id, sum(income) sum_income
from mytable
group by customer_id
having max(year(transaction_date ) <> 2014) = 1

    This gives you the total income per customers, while filtering out customers that had transactions in 2014 only.