SQL Refer to alias in subquery

Question

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?

the subquery returns mre than one result that is the amin reason your sib query is nt workinhg besides that you can not use the alias in that position — nbk, Oct 16 '20 at 15:45

score 1 · Accepted Answer · answered Oct 16 '20 at 15:21

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.

SQL Refer to alias in subquery

1 Answers1