Santana Santana - 5 months ago 10
PHP Question

Mysql query for multiple fields values

Can anyone help with mysql query that show multiple fields based on their calculation.

I have the following data:

ID | Supplier | Total | Tax | Shipping | Paid
1 | 2 | 100 | 10 | 5 | 0
2 | 2 | 50 | 5 | 2.5 | 1


Now I want a result like the following:

Supplier | Total Paid | Total Unpaid
2 | 57.5 | 115


My query as follow (so far)

SELECT suppliers.company, SUM(tax+shipping+total) as unpaid FROM poreceived LEFT JOIN suppliers ON suppliers.id = poreceived.supplierid WHERE paid = '0' GROUP BY supplierid


I don't now how to show the paid value.

Any help is appreciated.

Answer
SELECT 
    supplierid,
    SUM(if(paid=1,total,0))+SUM(if(paid=1,tax,0))+SUM(if(paid=1,shipping,0)) AS Paid,
    SUM(if(paid=0,total,0))+SUM(if(paid=0,tax,0))+SUM(if(paid=0,shipping,0)) AS Unpaid
FROM poreceived
GROUP BY supplierid

OR

SELECT 
    supplierid,
    total_paid + shipping_paid + tax_paid as Paid,
    amount - total_paid - shipping_paid - tax_paid as Unpaid
FROM (
    SELECT 
        supplierid
        total + tax + shipping AS amount,
        SUM(if(paid=1,total,0)) AS total_paid,
        SUM(if(paid=1,shipping,0)) AS shipping_paid,
        SUM(if(paid=1,tax,0)) AS tax_paid
    FROM poreceived
    GROUP BY supplierid
) t
Comments