Mysql query for multiple fields values

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.

Community · Accepted Answer · 2017-05-23 12:22:40Z

2

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'?

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered Jun 19, 2016 at 4:25

gaheinrichs

6101 gold badge6 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

HeadwindFly · Accepted Answer · 2016-06-19 05:16:38Z

1

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

edited Jun 19, 2016 at 5:16

answered Jun 19, 2016 at 4:25

HeadwindFly

8943 gold badges10 silver badges20 bronze badges

Comments

Tim Biegeleisen · Accepted Answer · 2016-06-19 04:26:55Z

1

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

answered Jun 19, 2016 at 4:26

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Collectives™ on Stack Overflow

Mysql query for multiple fields values

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related