rony rony - 22 days ago 6
MySQL Question

I want to get each month expense from three table

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.


Blockquote

Answer

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.

Comments