CODEWITHSUNDEEP
mysql
Nicholas David
Nicholas David

Reputation: 33

Get sum across multiple tables

Table example:

branch
----------------------------------
branchID | branchName | brMgrName|
----------------------------------
|      1  | a         |Tom       |
|      2  | b         |Jeff      |
|      3  | c         |Lin       |
----------------------------------

order
----------------------
orderID   | branchID |
----------------------
|   1     |     2   | 
|   2     |     2   | 
|   3     |     3   | 
----------------------


payment
--------------------------------
paymentID | orderID | p-amount |
--------------------------------
|   1     |     1   |   50.00  |
|   2     |     2   |  126.00  |
|   3     |     3   |   50.00  |
--------------------------------

The output that I want are

-------------------------------------------
| branchName | brMgrName | Total Amount   |
-------------------------------------------
|      a     |  Tom      |     0.00       |
|      b     |  Jeff     |   252.00       |
|      c     |  Lin      |    50.00       |
-------------------------------------------


I am trying to determine how to sum up the amount from each branch across 3 different tables.

In the output:
- branchName 'a' don't have any value from the order table,
- branchName 'b' was sum up by paymentID 1 & 2, and the same with
- branchName 'c'

I have successfully return both the branchName and Total Amount by joining branch and payment tables:

SELECT `order`.branchID, sum(`p-amount`)
FROM `order`
JOIN payment
ON `order`.orderID = payment.orderID
GROUP BY branchID

But when i attempt the following code, it return the wrong value and only 1 row have selected.

SELECT branchName, brMgrName, sum(`p-amount`)
FROM branch, payment
JOIN (SELECT `order`.branchID, sum(`p-amount`)
      FROM `order`
      JOIN payment
      ON `order`.orderID = payment.orderID
      GROUP BY branchID) b
ON b.branchID = branchID;

Any help is very much appreciated.

Upvotes: 3

Views: 61

Answers (3)

Ire
Ire

Reputation: 279

try this?

SELECT `branch`.`branchID`, `branch`.`branchName`, `branch`.`brMgrName`, SUM(`payment`.`p-amount`)
FROM `branch`
JOIN `order` ON `branch`.`branchID` = `order`.`branchID`
JOIN `payment` ON `order`.`orderID` = `payment`.`orderID`
GROUP BY `branch`.`branchID`

Upvotes: 1

Just code
Just code

Reputation: 13801

SELECT b.branchName,b.brMgrName,
ISNULL( (SELECT sum(p.pamount) FROM dbo.payment p   
WHERE p.orderID IN (SELECT o.orderid FROM dbo.[order] o
WHERE o.branchID=b. branchID)),0) FROM dbo.branch b

Using Subquery

Upvotes: 1

Jorge Campos
Jorge Campos

Reputation: 23361

Try this:

SELECT b.branchName, brMgrName, sum(p.`p-amount`)
  FROM branch b
        LEFT JOIN `order` o
                ON b.branchID = o.branchID 
        LEFT JOIN payment p
                ON o.orderID = p.orderID 
GROUP BY b.branchName, brMgrName
ORDER BY 3 -- third column on select list

Upvotes: 1

Related Questions