How to display the paid and unpaid amount of Order in MySQL

Question

CUSTOMER TABLE

+------------+----------+-------+-------+-------------+-----------------------------+----------+--------------+
| CustomerID | Fname    | Mname | Lname | Contact_no  | Address                     | Valid_id | Credit_Limit |
+------------+----------+-------+-------+-------------+-----------------------------+----------+--------------+
|          7 | John     | Dale  | Doe   | 09123654789 || NULL     |     5000.000 |
|          8 | Jane     | Dale  | Doe   | 09987654123 |  | NULL     |     1500.000 |
|         91 | Kurdapya | Buang | Selos | 09741258963 |  | NULL     |     5000.000 |
+------------+----------+-------+-------+-------------+-----------------------------+----------+--------------+

ORDER TABLE

+---------+------------+----------------+----------+------------+
| OrderID | CustomerID | DateOfPurchase | Discount | DueDate    |
+---------+------------+----------------+----------+------------+
|      82 |          7 | 2022-04-17     | 0        | 2022-05-17 |
|      83 |         91 | 2022-04-17     | 0        | 2022-05-17 |
|      84 |          8 | 2022-04-17     | 0        | 2022-05-17 |
|      85 |         91 | 2022-04-17     | 0        | 2022-05-17 |
|      86 |          7 | 2022-04-17     | 0        | 2022-05-17 |
|      87 |         91 | 2022-04-18     | 0        | 2022-05-18 |
|     109 |          7 | 2022-04-25     | 0        | 2022-05-25 |
+---------+------------+----------------+----------+------------+

PAYMENT TABLE

+-----------+------------+---------+------------+----------+
| PaymentID | CustomerID | OrderID | PayDate    | Amount   |
+-----------+------------+---------+------------+----------+
|        20 |          7 |      82 | 2022-04-25 |  800.000 |
|        21 |         91 |      83 | 2022-04-17 | 2500.000 |
|        22 |         91 |      85 | 2022-04-17 |  200.000 |
|        23 |         95 |      88 | 2022-04-18 | 2122.000 |
|        24 |         96 |      90 | 2022-04-25 |  577.000 |
|        25 |         97 |     111 | 2022-04-25 |    0.000 |
|        26 |         98 |     114 | 2022-04-25 |  166.000 |
|        27 |         99 |     115 | 2022-04-25 | 1740.000 |
+-----------+------------+---------+------------+----------+

ORDER DETAILS TABLE

+-------+---------+-----------+------+
| OR_ID | OrderID | ProductID | QTY  |
+-------+---------+-----------+------+
|   173 |      82 |         5 |    1 |
|   174 |      82 |         9 |    1 |
|   184 |      86 |         5 |    1 |
|   185 |      86 |         9 |    1 |
|   186 |      86 |        13 |    1 |
|   187 |      86 |        17 |    1 |
|   224 |     109 |         3 |    3 |
|   225 |     109 |         6 |    3 |
|   292 |     145 |        20 |    2 |
|   293 |     145 |        12 |    1 |
|   294 |     145 |         8 |    2 |
|   295 |     146 |        14 |    1 |
|   296 |     146 |        11 |    1 |
|   297 |     146 |        12 |    1 |
|   298 |     146 |         3 |    1 |
|   299 |     146 |         6 |    1 |
|   300 |     146 |         7 |    1 |
|   301 |     146 |        16 |    1 |
+-------+---------+-----------+------+

I don't know if this is needed but this is my PRODUCT TABLE:

+-----------+---------------+-----------------------+------------+-----------+------+
| ProductID | Pname         | Pdesc                 | PUnitPrice | weight    | Unit |
+-----------+---------------+-----------------------+------------+-----------+------+
|         2 | Pepsi         | 1 Case Glass Bottle   |    313.000 | 1 Litre   |   12 |
|         3 | Mountain Dew  | 1 Case Glass Bottle   |    231.000 | 750 ML    |   12 |
|         4 | Pepsi         | 1 Case Plastic Bottle |    620.000 | 1.5 Litre |   12 |
|         5 | Mirinda       | 1 Case Plastic Bottle |    620.000 | 1.5 Litre |   12 |
|         6 | Mountain Dew  | 1 Case Plastic Bottle |    620.000 | 1.5 Litre |   12 |
|         7 | Mountain Dew  | 1 Case Glass Bottle   |    145.000 | 8 oz      |   24 |
|         8 | Pepsi         | 1 Case Glass Bottle   |    145.000 | 8 oz      |   24 |
|         9 | Mirinda       | 1 Case Glass Bottle   |    145.000 | 8 oz      |   24 |
|        10 | 7up           | 1 Case Glass Bottle   |    145.000 | 8 oz      |   24 |
|        11 | Sting         | 1 Case Glass Bottle   |    266.000 | 240 ml    |   24 |
|        12 | Tropicana     | 1 Case Glass Bottle   |    266.000 | 240 ml    |   24 |
|        13 | Cobra         | 1 Case Glass Bottle   |    266.000 | 240 ml    |   24 |
|        14 | Sting         | 1 Case Plastic Bottle |    166.000 | 300 ml    |   12 |
|        15 | Cobra         | 1 Case Plastic Bottle |    166.000 | 300 ml    |   12 |
|        16 | Mountain Dew  | 1 Case Plastic Bottle |    135.000 | 295 ml    |   12 |
|        17 | Mirinda       | 1 Case Plastic Bottle |    135.000 | 295 ml    |   12 |
|        18 | Pepsi         | 1 Case Plastic Bottle |    135.000 | 295 ml    |   12 |
|        19 | Ginebra       | 1 Case Glass Bottle   |    129.000 | 350 ml    |   24 |
|        20 | San Mig Light | 1 Case Glass Bottle   |   1070.000 | 330 ml    |   24 |
|        21 | Red Horse     | 1 Case Glass Bottle   |    535.000 | 500 ml    |   12 |
|        22 | Red Horse     | 1 Case Glass Bottle   |    545.000 | 1 Litre   |    6 |
+-----------+---------------+-----------------------+------------+-----------+------+

WHAT I WANT TO HAPPEND

I want show all the paid order of customerID 7 and his unpaid orders in one row only. I don't know how to start it with a query. Should I use an If() condition? How to properly query it to achieve my desire output?

My Desired OUTPUT:

+--------+------+-------+-------+-------+-------------+-----------------------------+--------------+----------+
| unpaid | paid | Fname | Mname | Lname | Contact_no  | Address                     | Credit_Limit | total    |
+--------+------+-------+-------+-------+-------------+-----------------------------+--------------+----------+
| 2995   | 6014 | John  | Dale  | Doe   | 09123654789 |.                   |     5000.000 | 9009.000 |
+--------+------+-------+-------+-------+-------------+-----------------------------+--------------+----------+

EDIT

THIS IS WHAT I'VE TRIED SO FAR

1st attempt: I am trying to calculate the unpaid order and this is the output:

select 
    if(py.Amount IS NULL, sum(od.QTY * p.PUnitPrice), CONCAT(py.Amount - sum(od.QTY * p.PUnitPrice))) as remarks, 
    c.Fname, c.Mname, c.Lname, c.Contact_no, c.Address, c.Credit_Limit, 
    sum(od.QTY * p.PUnitPrice) as total 
from customer c INNER JOIN orders r on r.CustomerID=c.CustomerID
INNER join order_details od on r.OrderID=od.OrderID 
INNER JOIN product p on od.ProductID = p.ProductID 
join payment py 
where c.CustomerID=7 

RESULT OF ATTEMPT 1:

+-------------+-------+-------+-------+-------------+-----------------------------+--------------+------------+
| remarks     | Fname | Mname | Lname | Contact_no  | Address                     | Credit_Limit | total      |
+-------------+-------+-------+-------+-------------+-----------------------------+--------------+------------+
| -134335.000 | John  | Dale  | Doe   | 09123654789 |                       |     5000.000 | 135135.000 |
+-------------+-------+-------+-------+-------------+-----------------------------+--------------+------------+

2nd attempt:

SELECT IF(py.OrderID IS NULL, sum(od.QTY * p.PUnitPrice), 0) AS unpaid,
if(py.OrderID IS NOT NULL, sum(od.QTY * p.PUnitPrice), 0) as paid,
sum(od.QTY * p.PUnitPrice) as total,
O.OrderID, O.CustomerID, date_format(O.DateOfPurchase, '%M %d, %Y') AS DateOfPurchase, date_format(O.DueDate, '%M %d, %Y') AS DueDate
from Orders O INNER JOIN order_details od on od.OrderID=O.OrderID
INNER JOIN product p ON od.ProductID=p.ProductID
LEFT JOIN Payment py ON py.OrderID = O.OrderID
where O.CustomerID = 7
GROUP by O.OrderID
ORDER by O.OrderID desc

RESULT OF ATTEMPT 2:

+----------+----------+----------+---------+------------+----------------+--------------+
| unpaid   | paid     | total    | OrderID | CustomerID | DateOfPurchase | DueDate      |
+----------+----------+----------+---------+------------+----------------+--------------+
| 1829.000 |    0.000 | 1829.000 |     146 |          7 | April 27, 2022 | May 27, 2022 |
|    0.000 | 2696.000 | 2696.000 |     145 |          7 | April 27, 2022 | May 27, 2022 |
|    0.000 | 2553.000 | 2553.000 |     109 |          7 | April 25, 2022 | May 25, 2022 |
| 1166.000 |    0.000 | 1166.000 |      86 |          7 | April 17, 2022 | May 17, 2022 |
|    0.000 |  765.000 |  765.000 |      82 |          7 | April 17, 2022 | May 17, 2022 |
+----------+----------+----------+---------+------------+----------------+--------------+

Note: How to sum all the rows in column paid and unpaid?

Answer 1

The way you are approaching the solution is obviously not working, hence you are asking for help. Your first query, you are joining to the payment table but no condition limiting to only payments for customer 7. This might coincidentally work out because there is only one payment record. But for customers with MORE than one, your totals will get skewed via Cartesian product result.

What you should do is pre-aggregate purchased completely separate from payments so you will have AT MOST, a single record for the one customer you are looking for. Now, if you want to apply the results for ALL customers, that just gets a little extra and will cover later.

With each individually, prevents confusion of multiple clutterings going on.

In its SHORTEST, since you only care about the TOTALS Purchased vs Paid, I would NOT care about the totals per EACH order, just the totals per customer.

select
        c.Fname, 
        c.Mname, 
        c.Lname, 
        c.Contact_no, 
        c.Address, 
        c.Credit_Limit, 
        coalesce( PQ_Orders.TotalOrders, 0 ) TotalOrders,
        coalesce( PQ_Payments.TotalPaid, 0 ) TotalPayments,
        coalesce( PQ_Orders.TotalOrders, 0 )
            - coalesce( PQ_Payments.TotalPaid, 0 ) BalanceDue
    from 
        customer c 
            LEFT JOIN
            ( select
                    o.customerID,
                    sum( od.qty * p.pUnitPrice ) TotalOrders
                from
                    orders o
                        join order_details od
                            on o.orderID = od.orderID
                            join product p
                                on od.productid = p.productid
                where
                    o.customerid = 7
                group by
                    o.customerID ) PQ_Orders
                on c.customerID = PQ_Orders.customerID
            LEFT JOIN
            ( select
                    py.customerid,
                    sum( py.Amount ) TotalPaid
                from
                    payments py
                where
                    py.customerid = 7
                group by
                    py.customerid ) PQ_Payments
                on c.customerID = PQ_Payments.CustomerID
    where 
        c.CustomerID = 7 

Now, if you want for ALL customers, just remove all the WHERE clauses so it gets ALL customers. Each respective inner query gets the customer ID as the data grouping, so if one person has 10 orders and 3 payments, you still have at the per PRE-QUERY (PQ) aggregations, only 1 entry per customer on orders and 1 entry per customer on payments. Joined back to the main customer table and its applied across-the-board to all.

CLARIFICATIONS to O/P

To help clarify, lets look at what you are asking for. For a single customer, how much stuff was purchased. Think of that as one query. Get all order for that one customer, per the order details and the respective product price and sum it up grouped by the customer. Now the WHERE clause = 7 for the customer is just because you are about the ONE customer. But doing without the where would show ALL customers what all their respective purchases were. You would have AT MOST (because group by customerid) 1 record per customer regardless of them having 1 order, or 274 orders. Its the total for that customer.

Now, the same exact context for payments. I dont care what or when they purchased something. All I care about is payments made by a given customer. Similarly with or without the WHERE of customer ID = 7 as in purchases explanation. Again, resulting in a record of only 1 per customer regardless of 1 or 75 payments, 1 record per customer.

So now, you have a 1:1:1 possible ratio of 1 customer to 1 purchase to POSSIBLY 1 payment total.

The LEFT JOIN means, I want the thing to the left (customer), but OPTIONALLY may find something on the right (purchases AND/OR payments).

So the COALESCE() prevents nulls from tripping things up in the calculations. If there IS a value from the given pre-query of purchases or payments, get it, otherwise, assume zero. So you could get just the one customer in question, or by removing the WHERE clauses, you could get ALL customers with ALL their total purchases AND ALL their total payments for a full outstanding balance of ALL customers.