Joining tables in MySQL where columns are summed

Question

I am trying to join 3 tables. Two of the tables I am taking sums of a column. I want to apply conditions on the sums but am not producing the result I want with the below script. The sums are not summing correctly.

SELECT 
account_list.Account_ID,
account_list.Account_Name,
account_list.Short_Name,
account_list.Trader,
account_list.Status,
account_list.Notes,
sum(account_commissions.Commission),
sum(connection_cost.Monthly_Cost)
FROM
account_commissions
Join 
connection_cost
ON 
account_commissions.Account_ID = connection_cost.Account_ID
AND 
connection_cost.Cost_Date > '2013-06-01'
AND 
account_commissions.TDate > '2013-06-01'    
Right Join 
account_list
ON
account_list.Account_ID = connection_cost.Account_ID
WHERE
account_list.status = 'Active'
GROUP BY 
Account_ID;

The conditions I want on the sums are:

sum account_commissions.Commission where account_commissions.TDate > '2013-06-01 Group BY Account_ID 

and

sum connection_cost.Monthy_Cost where connection_cost.Date > '2013-06-01' Group BY Account_ID. 

I tried to achieve that using the above AND statements but it is not computing correctly. Any help on how to apply these conditions to the sum columns would be appreciated.

Answer 1

I've changed to a LEFT-JOIN as it appears you want all account list entries, and any corresponding summation of costs and commissions per account. So, the JOINs are based on sum() of each table individually, but grouped by account, THEN joined back to the main account list table.

SELECT
      AL.Account_ID,
      AL.Account_Name,
      AL.Short_Name,
      AL.Trader,
      AL.Status,
      AL.Notes,
      coalesce( preSumCC.CC_Costs, 0 ) as MonthlyCosts,
      coalesce( preSumComm.AC_Commission, 0 ) as Commissions
   FROM
      account_list AL
         LEFT JOIN ( SELECT CC.Account_ID, 
                            SUM( CC.Monthly_Cost ) CC_Costs
                        FROM
                           connection_cost CC
                        where 
                           CC.Cost_Date > '2013-06-01'
                        group by
                           CC.Account_ID ) preSumCC
            ON AL.Account_ID = preSumCC.Account_ID

         LEFT JOIN ( select AC.Account_ID,
                            SUM( AC.Commission ) AC_Commission
                        FROM
                           account_commissions AC
                        where
                           AC.TDate > '2013-06-01'
                        group by
                           AC.Account_ID ) preSumComm
            ON AL.Account_ID = preSumComm.Account_ID

Answer 2

You can create a conditional SUM in MySQL using IF:

SUM(IF(account_commisions.TDate >'2013-01-01',account_commissions_Commission, 0))

To be more portable, you should use CASE, as IF is not part of the SQL standard. I tend to find IF more readable though.