SQL query getting error in-line - Unknown column in 'field list'

Question

I want to get sum of row values in a single column by using in-line SQL statement.

I used the following command to do this.

(SELECT @fee := sum(ifnull(issued_amount, 0.00)) AS tot FROM fees_payback_fees de2 
            GROUP BY de2.fees_fees) 

This is working fine for me in phpmyadmin and outs the "tot" corectly.

But I used this as in-line query in the following SQL Statement, it outs an error as follows:

Unknown column 'tot' in 'field list'

SELECT

            fees_id,
            locgov_id,
            locgov_name,
            locgov,
            added_date,
            month,            
            fee,
            type,
            cs,
            ps,
            id,
            tot
            

            FROM (
            SELECT 
            fees_fees.fees_id, fees_locgov.locgov_id AS locgov_id,
            fees_locgov.locgov_name AS locgov_name, fees_fees.locgov,        
            fees_fees.added_date as added_date, fees_fees.month as month, fees_fees.fee as fee,
            fees_fees.type, fees_fees_verify.status AS cs, fees_fees.transfer_status AS ps,
            fees_payback_fees.fees_fees AS id,         

            (SELECT @fee := sum(ifnull(issued_amount, 0.00)) AS tot FROM fees_payback_fees de2 
            GROUP BY de2.fees_fees)  
            
            from fees_fees
            
            left join fees_fees_verify on fees_fees_verify.fees_id=fees_fees.fees_id
            left join fees_locgov on fees_locgov.locgov_id=fees_fees.locgov
            left join fees_payback_fees on fees_payback_fees.fees_fees=fees_fees.fees_id
            
            where fees_fees.status=1 
            AND fees_fees_verify.status='Confirmed' 
             
            ) tmp group by id order by fees_id DESC

What may be going wrong ? Can anyone help ?

Answer 1

You provide the tot alias in an uncorrelated subquery in the select list of tmp derived table. The subquery is used to return a column. However, the subquery itself does not have an alias and the alias within the subquery is not used as a column alias outside of the subquery if the subquery is in the select list.

You should place the tot alias after the subquery to indicate its name as a column name:

...
(SELECT @fee := sum(ifnull(issued_amount, 0.00)) AS tot FROM fees_payback_fees de2 
        GROUP BY de2.fees_fees)  as tot
...

Although, I have a gut feeling that you may get a subquery return more than one row error after this. I think you either have to make this subquery a corralated one or you need to move it to the from list as a derived table.