CODEWITHSUNDEEP
mysql
Debs
Debs

Reputation: 341

#1054 - Unknown column in 'field list'

I am back with another problem. I know there are many links related to this issue but unable to find the exact solution for my query. Here is my query :-

SELECT c.cust_id, c.cust_name, c.cust_mob, sum(CASE WHEN trans_type = 'Purchase' THEN total_amt ELSE 0 END) as purchase, sum(CASE WHEN trans_type = 'Sale' THEN total_amt ELSE 0 END) as sale, sum(ifnull(a.payment_amt,0)) as tot_pay, (purchase-(sale+sum(a.payment_amt))) as tot_torcv, ((sale+sum(a.payment_amt))-purchase) as tot_topay FROM bil_customers c 
    inner join bil_vendor_account a on(c.cust_id=a.vendor_id) 
    WHERE c.cust_catagory = '3' 
    group by cust_id 
    having ifnull(tot_torcv,0) between '0' and '100000' 
    order by a.sl_no

I dont know what is wrong with the query as it is throwing the following error:-

1054 - Unknown column 'purchase' in 'field list'

Please help me sorting out the problem. Thanks in advance!!

Upvotes: 0

Views: 1909

Answers (3)

M. Page
M. Page

Reputation: 2814

Additionnally to what CodeSlays writes, <expression> between '0' and '100000' will not work as you intend because it will perform string comparison and not numeric comparison (remind: '9' > '10',).

Upvotes: 0

CodeSlayer
CodeSlayer

Reputation: 1329

Your error is, you are using the purchase as column even though it is alias you can use this instead

Not tested

SELECT c.cust_id, c.cust_name, c.cust_mob, @purchase :=sum(CASE WHEN trans_type = 'Purchase' THEN total_amt ELSE 0 END) as purchase, sum(CASE WHEN trans_type = 'Sale' THEN total_amt ELSE 0 END) as sale, sum(ifnull(a.payment_amt,0)) as tot_pay, (@purchase-(sale+sum(a.payment_amt))) as tot_torcv, ((sale+sum(a.payment_amt))-@purchase) as tot_topay FROM bil_customers c 
inner join bil_vendor_account a on(c.cust_id=a.vendor_id) 
WHERE c.cust_catagory = '3' 
group by cust_id 
having ifnull(tot_torcv,0) between '0' and '100000' 
order by a.sl_no

just let me know if i miss something

Upvotes: 1

Jens
Jens

Reputation: 69440

You can not use the alias createtd in the select statement to calculate other columns. you ave to replace the alias with the calculation

SELECT c.cust_id, c.cust_name, c.cust_mob, sum(CASE WHEN trans_type = 'Purchase' THEN total_amt ELSE 0 END) as purchase, sum(CASE WHEN trans_type = 'Sale' THEN total_amt ELSE 0 END) as sale, sum(ifnull(a.payment_amt,0)) as tot_pay, (sum(CASE WHEN trans_type = 'Purchase' THEN total_amt ELSE 0 END)-(sale+sum(a.payment_amt))) as tot_torcv, ((sale+sum(a.payment_amt))-sum(CASE WHEN trans_type = 'Purchase' THEN total_amt ELSE 0 END)) as tot_topay FROM bil_customers c 
    inner join bil_vendor_account a on(c.cust_id=a.vendor_id) 
    WHERE c.cust_catagory = '3' 
    group by cust_id 
    having ifnull(tot_torcv,0) between '0' and '100000' 
    order by a.sl_no

Upvotes: 0

Related Questions