Balakrishnan S Balakrishnan S - 4 months ago 9
SQL Question

Applying mysql conditions

I have used two tables invoice and payment.
I trying to generate paid, due and unpaid status.

Follwing is my query, This will give all invoices and total payments.

How can i apply a condition to filter paid, due, unpaid.

I cant give as paidamount

Follwing is successfull query:

select a.*, sum(b.amount) as paidamount
from tbl_invoices a
left join tbl_billpayment b on a.invoiceno = b.invoiceno where a.id != ''
GROUP BY b.invoiceno
ORDER BY a.billdate DESC
LIMIT 0,10


This will give error

select a.*, sum(b.amount) as paidamount
from tbl_invoices a
left join tbl_billpayment b on a.invoiceno = b.invoiceno
where a.id != '' and ( paidamount >= a.total)



Notice: Error: Unknown column 'paidamount' in 'where clause'


Thank you for your support

Answer

The problem is in this condition.

( paidamount >= a.total)

instead use like this

(sum(b.amount) >=a.total)

paidamount is just an alias that will replace name while projection.

Comments