How to select mysql SUM from multiple tables and other columns from another table?

Question

I have three tables: cash, cheque and bill. All three tables share two common columns: billId and customerId.

Bill Table

+--------+------------+---------+------------+
| billId | date       | bAmount | customerId |
+--------+------------+---------+------------+
| #1     | 01-05-2016 | 120.00  | 2          |
+--------+------------+---------+------------+
| #2     | 10-05-2016 | 100.00  | 2          |
+--------+------------+---------+------------+
| #3     | 20-05-2016 | 80.00   | 2          |
+--------+------------+---------+------------+
| #4     | 20-05-2016 | 70.00   | 2          |
+--------+------------+---------+------------+
| #5     | 27-05-2016 | 50.00   | 2          |
+--------+------------+---------+------------+
| #6     | 28-05-2016 | 20.00   | 2          |
+--------+------------+---------+------------+

Cheque Table

+----------+--------+------------+----------+
| chequeId | billId | customerId | chAmount |
+----------+--------+------------+----------+
| 1        | #1     | 2          | 50.00    |
+----------+--------+------------+----------+
| 2        | #2     | 2          | 25.00    |
+----------+--------+------------+----------+
| 3        | #5     | 2          | 36.00    |
+----------+--------+------------+----------+
| 4        | #4     | 2          | 23.00    |
+----------+--------+------------+----------+

Cash Table

+--------+--------+------------+----------+
| cashId | billId | customerId | caAmount |
+--------+--------+------------+----------+
| 1      | #1     | 2          | 55.00    |
+--------+--------+------------+----------+
| 2      | #2     | 2          | 70.00    |
+--------+--------+------------+----------+
| 3      | #3     | 2          | 69.00    |
+--------+--------+------------+----------+
| 4      | #4     | 2          | 23.00    |
+--------+--------+------------+----------+

I have to generate a query to generate results like below:

+--------+------------+--------+---------+
| billId | date       | amount | pending |
+--------+------------+--------+---------+
| #1     | 01-05-2016 | 120.00 | 15.00   |
+--------+------------+--------+---------+
| #2     | 10-05-2016 | 100.00 | 05.00   |
+--------+------------+--------+---------+
| #3     | 20-05-2016 | 80.00  | 11.00   |
+--------+------------+--------+---------+
| #4     | 20-05-2016 | 70.00  | 14.00   |
+--------+------------+--------+---------+
| #5     | 27-05-2016 | 50.00  | 04.00   |
+--------+------------+--------+---------+

I am sending a value for customerID to this page from another page, like $customerId = $_REQUEST['customerId'] and from this I have to select BillId and Date from the Bill Table, amount (which is computed by the sum of chAmount+caAmount), and pending (which is computed by the difference of bAmount-(chAmount+caAmount)). Since billId #6 doesn't have any records in the cheque and cash tables it doesn't need to be yielded in the results. Please mention a proper MySql query and explain it.

Delphine · Accepted Answer · 2016-10-21 08:17:27Z

1

Normally, you should try something but as I am in my "good days" I wrote the full SQL statement :

SELECT b.billId, b.bDate, b.bAmount, SUM(ch.chAmount) chAmountSum, SUM(ca.caAmount) caAmountSum, (b.bAmount - ( IFNULL(SUM(ch.chAmount), 0)  + IFNULL(SUM(ca.caAmount), 0))) pending
FROM bill b
LEFT JOIN  cheque ch on ch.billId = b.billId
LEFT JOIN  cash ca on ca.billID = b.billId
GROUP BY b.billID;

You have to select all columns that you want to display
For the last one (pending), make your operation (amount - (cheque + cash)) and if it is a null value, replace it by "0" thanks to IFNULL SQL function
Use LEFT JOIN because you want all bills even if there is no associated payment yet (cheque / cash)
GROUP BY from your reference column : billID

edited Oct 21, 2016 at 8:17

answered Oct 20, 2016 at 13:58

Delphine

8989 silver badges24 bronze badges

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

3 Comments

user466061 Over a year ago

I was trying to do this around 4 hours and at last you gave me a wonderful solution, thanks a lot. and where did you learn SQL? ;)

user466061 Over a year ago

i need WHERE='customerId' also?

Delphine Over a year ago

I learned it at school and I loved it ;) ! Yes, if you want to filter by specific customer just add a WHERE clause. But, you don't need it if you want a global result (for all of your customers) beacause tables are already linked by unique billId and a bill is linked to only one customer.

Collectives™ on Stack Overflow

How to select mysql SUM from multiple tables and other columns from another table?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related