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.
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.
Would this work? SELECT convert(varchar(8), bill.B_Date,120) +'01', 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 group by convert(varchar(8), bill.B_Date,120) +'01'