我想从三张桌子上得到每个月的费用

Question

I have three table named "salary", "allowance" and "bill".

"salary" table has

 s_id [primary key], E_ID, S_Amount, S_Date.

"allowanace" table has

 A_ID[primary key], E_ID, A_TA, A_DA, A_MA, A_Others, A_Date, Total_A

"bill" table has

 E_ID[primary key], Electric, Gas, Water, B_Others, B_Date Total_B 

I have joined these tables with this query>>

 SELECT 
     SUM(allowance.`A_TA`+allowance.`A_DA`
     +allowance.`A_MA`+allowance.`A_Others`)
     AS Total_Allowance, 
     (SUM(salary.`S_Amount`)) AS Total_Salary, 
      SUM(bill.`Electric`+bill.`Water`+bill.`Gas`+bill.`B_Others`) as 
      Total_Bill,
     (SUM(allowance.`A_TA`+allowance.`A_DA`+allowance.`A_MA`
      +allowance.`A_Others`)+SUM(salary.`S_Amount`)
      +SUM(bill.`Electric`+bill.`Water`+bill.`Gas`+bill.`B_Others`))
      AS Total_Ex 
      from salary
      INNER JOIN allowance
      ON salary.E_ID=allowance.E_ID 
      INNER JOIN bill
      ON salary.S_Date=bill.B_Date

I want to get the total expense "Total_Ex" of january 2015, December 2015, October 2016, november 2016...each month's total expense separately.

Answer 1

Try using SUM and IF:

SUM(IF(DATE_FORMAT(salary.S_Date,'%M')='January',
allowance.A_TA+allowance.A_DA+allowance.A_MA
+allowance.A_Others
+salary.S_Amount+bill.Electric+bill.Water
+bill.Gas+bill.B_Others,0)) AS 
Jan_Total_Ex

This will give you Jan total expense.