How do I sum a column within a join statement?

Question

I'm trying to join several tables and I need to return a summed value of some of the columns. I've tried using SUM but it isn't behaving like I would expect with the JOIN statements.

My tables are set up something like this:

daily_balances
+----------------+-------------+-------------+
| account_number | balance_date| total_value |
+----------------+-------------+-------------+
|    1           | 01/01/2018  |  100.00     |
|    1           | 01/17/2018  |  250.00     |
|    1           | 02/22/2018  |  175.00     |

account_inflows
+----------------+-------------+-------------+
| account_number | inflow_date | total_value |
+----------------+-------------+-------------+
|    1           | 01/01/2018  |  100.00     |
|    1           | 01/01/2018  |   50.00     |
|    1           | 01/17/2018  |   75.00     |

account_outflows
+----------------+-------------+-------------+
| account_number | inflow_date | total_value |
+----------------+-------------+-------------+
|    1           | 02/22/2018  |  100.00     |

Running this statement:

select `daily_balances`.`balance_date` as `date`, `daily_balances`.`total_value` as `ending_value`, `account_inflows`.`total_value` as `account_inflow`, `account_outflows`.`total_value` as `account_outflow`, 
from `daily_balances` 
left join `account_inflows` on `daily_balances`.`account_number` = `account_inflows`.`account_number` and `daily_balances`.`balance_date` = `account_inflows`.`inflow_date` 
left join `account_outflows` on `daily_balances`.`account_number` = `account_outflows`.`account_number` and `daily_balances`.`balance_date` = `account_outflows`.`outflow_date` 
where `daily_balances`.`account_number` = 1 and `daily_balances`.`balance_date` = '2018-01-01'

returns:

+------------+--------------+----------------+-----------------+
| date       | ending_value | account_inflow | account_outflow |
+------------+--------------+----------------+-----------------+
| 01/01/2018 | 100.00       |     100.00     |       null      |
| 01/01/2018 | 100.00       |      50.00     |       null      |
| 01/17/2018 | 250.00       |      75.00     |       null      |
| 02/22/2018 | 175.00       |       null     |      75.00      |

I want to instead sum the account_inflow column to get this:

+------------+--------------+----------------+-----------------+
| date       | ending_value | account_inflow | account_outflow |
+------------+--------------+----------------+-----------------+
| 01/01/2018 | 100.00       |     150.00     |       null      |
| 01/17/2018 | 250.00       |      75.00     |       null      |
| 02/22/2018 | 175.00       |       null     |      75.00      |

Answer 1

Your current query only brings back the individual rows, but doesn't do anything to actually sum the columns.

select `daily_balances`.`balance_date` as `date`, 
    `daily_balances`.`total_value` as `ending_value`, 
    Sum(`account_inflows`.`total_value`) as `account_inflow`, 
    `account_outflows`.`total_value` as `account_outflow`
from `daily_balances` 
left join `account_inflows` on `daily_balances`.`account_number` = `account_inflows`.`account_number` and  
    `daily_balances`.`balance_date` = `account_inflows`.`inflow_date` 
left join `account_outflows` on `daily_balances`.`account_number` = `account_outflows`.`account_number` and 
    `daily_balances`.`balance_date` = `account_outflows`.`outflow_date` 
where `daily_balances`.`account_number` = 1 and 
    `daily_balances`.`balance_date` = '2018-01-01'
Group By `daily_balances`.`balance_date`

Since you don't want to sum All the values together, but rather just on the distinct dates, you'll need the Group By clause. ATM, I don't have a way to verify this is a perfectly correct query to do what you want, but it should get you in the right direction.

You can read more about this at: https://www.w3resource.com/sql/aggregate-functions/sum-with-group-by.php

Also, you should do more to format your queries to make them more readable. Code clarity is almost as important as having correct code, as having it readable will make fixing it, or just rereading it, much less of a mental exercise. I've formatted it to how I've been taught, but there are other methods out there to look into.