CODEWITHSUNDEEP
mysql
Hafiz K
Hafiz K

Reputation: 100

How to get sum from 2 different tables from another table

I'm working on a expenses/budgeting tracking system and stuck in some part.

I have 4 tables

tblProjects

enter image description here

tblCategory

enter image description here

tblExpenses

enter image description here

tblIncomes

enter image description here

tblProjects relate to tblCategory with 1:1,

tblExpenses relate to tblProjects also with 1:1,

same goes for tblIncomes which relate to tblProjects with 1:1

I'm trying to get the sum of expenses and incomes of each category group by the year(from column tblProjects.proj_sdate), plus a column that will show the P&L(expenses - incomes).

For instance I want to know what is the total amount of expenses, total amount of incomes, the value of P&L spent for Trip, Sports etc.

Below example of what I want to achieve;

enter image description here

I try to get with below query, but it returns me wrong data:

SELECT category.cat_title as Category,date_format(projects.proj_sdate, '%Y') 
as Year, (select sum(incomes.inc_amount) from incomes where 
incomes.projects_id = projects.proj_id) as Total_Income, 
(select sum(expenses.exp_amount) from expenses where expenses.projects_id = 
projects.proj_id) as Total_Expenses,
(select ifnull(sum(incomes.inc_amount),0) from incomes where 
incomes.projects_id = projects.proj_id) - (select 
ifnull(sum(expenses.exp_amount),0) from expenses where expenses.projects_id 
= projects.proj_id) as PnL from category inner join projects on 
projects.proj_cat = category.cat_id group by category.cat_id

Upvotes: 1

Views: 74

Answers (1)

Fahmi
Fahmi

Reputation: 37473

You can try below -

SELECT category.cat_title as Category,Proj_title,
date_format(projects.proj_sdate, '%Y') as Year,
sum(expenses.exp_amount)  as total_income
coalesce(sum(incomes.inc_amount),0) - sum(expenses.exp_amount) as pnl
from 
category inner join projects on projects.proj_cat = category.cat_id
inner join expenses on expenses.projects_id = projects.proj_id
inner join incomes on incomes.projects_id = projects.proj_id
group by category.cat_title,Proj_title,date_format(projects.proj_sdate, '%Y')

Upvotes: 1

Related Questions