Maricel Pamintuan Navarro Maricel Pamintuan Navarro - 4 months ago 14
SQL Question

Mysql sum column different 3 tables


funds_table
---------------------------
source_id, amount, date,
---------------------------
1, 1000, 2016-01-15
2, 2000, 2016-02-28



ca_table
---------------------------
ca_id,source_id, amount
---------------------------
c1 , 1, 500
c2 , 1, 500
c3 , 2, 900
c4 , 2, 1100



exp_table
---------------------------
exp_id, ca_id, amount,
---------------------------
e1, c1, 0
e2, c1, 250
e3, c2, 500
e4, c3, 500
e5, c4, 600
e6, c4, 500


I want to sum the select MONTHNAME(date) ,sum(funds_table.amount**) ,sum(ca_table.amount),sum(exp_table.amount)
join each other
where year(date) = 2016
and group by month(date)

expected output



-----------------------------------------------------------------------
MonthName(date),sum(source_id.amount),sum(ca_id.amount),sum(exp_id.amount)
-----------------------------------------------------------------------
Jan , 1000, 1000, 750
Feb , 2000, 2000, 1100


3 days search for result but i cant get the exact result.

Answer

I Don't Have Your Tables But I Think This will Help U

SELECT DATE_FORMAT(funds_table.date,"%M") AS month,SUM(ca_table.amount) AS ca_table_amount,SUM(exp_table.amount) AS exp_table_amount,month,SUM(funds_table.amount) AS funds_table_amount FROM `funds_table` INNER JOIN ca_table
ON funds_table.source_id=ca_table.source_id
INNER JOIN exp_table
ON ca_table.ca_id=exp_table.ca_id
GROUP BY month
Comments