CODEWITHSUNDEEP
mysql
Jimmuel Agwit
Jimmuel Agwit

Reputation: 3

Computing the sum of two rows with different id in MySQL

Good day,

I have a problem regarding computing the sum of rows in MySQL with a different id. Here is my table structure:

Table Orders

---------------------------
Transaction Id          Name
---------------------------
1                    John
2                    Jim
3                    Erick
4                    John
---------------------------

Table Details

---------------------------
Customer Id         Amount
---------------------------
1                    10.00
1                    20.00
2                    40.00
3                     5.00
3                     5.00
1                    50.00
---------------------------

I have tried joining the tables but the result only shows the total sum of all the rows with the same id.:

---------------------------------
Name                 Total Amount
---------------------------------
John                     80.00
Jim                      40.00
Erick                    10.00
---------------------------------

What I want to show on the result the per transaction :

----------------------------------
Name                 Total Amount
----------------------------------
John                 30.00
Jim                  40.00
Erick                10.00
John                 50.00
----------------------------------

Hope anyone can help me. Thank you

Upvotes: 0

Views: 43

Answers (1)

Barmar
Barmar

Reputation: 781004

You need an Order ID in the Orders table, and that should be used as the foreign key in Details. Your tables should look like this:

Table Orders

-----------------------------
Customer Id          Order ID
-----------------------------
1                    1
2                    2
3                    3
1                    4
---------------------------

Table Details

---------------------------
Order Id             Amount
---------------------------
1                    10.00
1                    20.00
2                    40.00
3                     5.00
3                     5.00
4                    50.00
---------------------------

Table Customers

---------------------
Customer Id     Name
1               John
2               Jim
3               Erick
--------------------

And then the query to get your results is:

SELECT o.order_id, c.name, SUM(d.amount) AS total
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
JOIN details AS d ON o.order_id = d.order_id
GROUP BY o.order_id, c.name

Upvotes: 1

Related Questions