CODEWITHSUNDEEP
phpmysql
Santana
Santana

Reputation: 232

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.

Upvotes: 1

Views: 109

Answers (3)

HeadwindFly
HeadwindFly

Reputation: 884

SELECT 
    supplierid,
    SUM(if(paid=1, total + tax + shipping, 0)) AS Paid,
    SUM(if(paid=0, total + tax + 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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

You can use a pivot query to get the output you want:

SELECT Supplier,
    SUM(CASE WHEN Paid = 1 THEN Total + Tax + Shipping ELSE 0 END) AS `Total Paid`,
    SUM(CASE WHEN Paid = 0 THEN Total + Tax + Shipping ELSE 0 END) AS `Total Unpaid`,
FROM poreceived
GROUP BY Supplier

Upvotes: 1

gaheinrichs
gaheinrichs

Reputation: 610

Try using and IF inside the SUM

SELECT
t.supplier,
SUM(IF(t.paid = 1, t.tax + t.shipping + t.total, 0)) AS total_paid,
SUM(IF(t.paid = 0, t.tax + t.shipping + t.total, 0)) AS total_unpaid
FROM poreceived AS t
GROUP BY t.supplier;

This questions is a bit related:

MySql: is it possibile to 'SUM IF' or to 'COUNT IF'?

Upvotes: 2

Related Questions