MySql: How to get SUM values from different tables?

Question

For each order I want get total for products and total of payments to get a balance.

This is a reduced version of tables:

ORDERS
--------------------
|ord_id|customer_id|
--------------------
|     1|        XYZ|
|     .|          .|
|     .|          .|
|     .|          .|
--------------------

ORDER_DETAILS
-----------------------------------------
|det_id|ord_id|product_id|quantity|price|
-----------------------------------------
|     1|     1|    AAA001|       3|   30|
|     2|     1|    BBB002|       2|    5|
|     .|     .|         .|       .|    .|
|     .|     .|         .|       .|    .|
|     .|     .|         .|       .|    .|
-----------------------------------------

PAYMENTS
----------------------
|pay_id|ord_id|amount|
----------------------
|     1|     1|    10|
|     2|     1|    20|
|     3|     1|    10|
|     .|     .|     .|
|     .|     .|     .|
|     .|     .|     .|
----------------------

This query does NOT return the correct values for the payments, only get a correct value for payments when count of products is the same for count of payments:

SELECT o.ord_id, SUM(quantity * price) AS total_order, SUM(amount) AS total_payments
FROM orders AS o 
INNER JOIN order_details AS d ON o.ord_id = d.ord_id
INNER JOIN payments AS p ON o.ord_id = p.ord_id
GROUP BY o.ord_id

This is the expected result:

-----------------------------------
|ord_id|total_order|total_payments|
-----------------------------------
|     1|        100|            40|
|     .|          .|             .|
|     .|          .|             .|
|     .|          .|             .|
-----------------------------------

Thanks in advance.

Thanatos · Accepted Answer · 2011-12-03 07:23:36Z

7

Do the two queries separately, and join the results. To me that makes much more sense logically:

SELECT
    ot.ord_id, 
    ot.order_total,
    op.order_paid
FROM
    (
        SELECT
            ord_id,
            SUM(price * quantity) AS order_total
        FROM
            ORDER_DETAILS
        GROUP BY
            ord_id
    ) AS ot
    INNER JOIN (
        SELECT
            ord_id,
            SUM(amount) AS order_paid
        FROM
            PAYMENTS
        GROUP BY
            ord_id
    ) AS op ON (op.ord_id = ot.ord_id)
;

…

 ord_id | order_total | order_paid 
--------+-------------+------------
      1 |         100 |         40
(1 row)

edited Dec 3, 2011 at 7:23

answered Dec 3, 2011 at 7:15

Thanatos

44.7k17 gold badges99 silver badges152 bronze badges

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

Comments

Tudor Constantin · Accepted Answer · 2011-12-03 07:24:47Z

0

Try grouping by det_id and a WITH ROLLUP clause also:

SELECT o.ord_id, SUM(quantity * price) AS total_order, SUM(amount) AS total_payments
FROM orders AS o 
INNER JOIN order_details AS d ON o.ord_id = d.ord_id
INNER JOIN payments AS p ON o.ord_id = p.ord_id
GROUP BY o.ord_id, o.det_id WITH ROLLUP

edited Dec 3, 2011 at 7:24

answered Dec 3, 2011 at 7:15

Tudor Constantin

26.9k7 gold badges54 silver badges73 bronze badges

1 Comment

Leonardo Over a year ago

I'm getting syntax error in WITH modifier (MySql version: 5.1.41). I check documentation (dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html) but I see the clause correct.

Collectives™ on Stack Overflow

MySql: How to get SUM values from different tables?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related