Santana - 1 year ago 50

PHP Question

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 Source

```
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
```