How to get desired results in mysql query? -

i use query fetch: customername tblcustomers sum of transactionamount tbltransactions against each customer

select a.customerid, sum(transactionamount) transactionamount ,b.customername tbltransactions  join tblcustomers b using (customerid)  group a.customerid  order b.customername 

the coloumn transactionamount in tbltransactions contains positive values customer purchases , negative values customer payments.

how expression in query min(transactionamount) < 0 each customer?

edit: min(transactionamount) < 0 gives me maximum amount paid far each customer

you can using conditional aggregation:

select a.customerid, sum(transactionamount) transactionamount, b.customername,        min(case when transactionamount < 0 transactionamount end) biggestpayment tbltransactions join      tblcustomers b      using (customerid) group a.customerid order b.customername; 

note smallest value less 0 minimum not maximum.

edit:

in case, find latest payment date using substring_index()/group_concat() trick:

substring_index(group_concat((case when transactionamount < 0 transactionamount end)                               order transactiondate desc                             ), ',', 1)