CODEWITHSUNDEEP
mysql
Pamela
Pamela

Reputation: 684

Mysql SUM And Group from multiple tables but only with certain data

I have 3 database tables as follows:

projects:

id

project_name

project_location

status

project_expenses

id

project_id

expense_category

expense_subcategory

amount

userid

date

payment_status

project_status

project_income

id

project_id

project_income

date

userid

project_status

projects.id, project_expenses.project_id and project_income.project_id are related.

I need to create a query displaying Project_ID,Project_Name,SUM of Project_Income,SUM of Project_expenses, Difference

I tried the following query.

SELECT p.id AS id, p.project_name AS project_name
,(SELECT SUM(i.project_income)
FROM project_income i
WHERE i.project_id = p.id 
)AS income,
(SELECT SUM(e.amount)
FROM project_expenses e
WHERE e.project_id = p.id 
)AS expenses,
(SELECT (income - expenses)) AS difference
FROM projects p
WHERE p.status = 'Active'

BUT some "projects without any income and expenses" are also listed. I need to display only those projects with expenses and incomes.

How is that possible. Requesting help...

Upvotes: 0

Views: 74

Answers (1)

Akina
Akina

Reputation: 42632

SELECT projects.id AS id, 
       projects.project_name AS project_name, 
       SUM(totals.income) income, 
       SUM(totals.expenses) expenses,
       SUM(totals.income) - SUM(totals.expenses) difference
FROM ( SELECT SUM(project_income) income,
              0 expenses,
              project_id
       FROM project_income i
       GROUP BY project_id
     UNION ALL
       SELECT 0,
              SUM(e.amount),
              project_id
       FROM project_expenses e
       GROUP BY project_id ) totals
JOIN projects ON totals.project_id = projects.id
WHERE projects.status = 'Active'
GROUP BY projects.id, 
         projects.project_name

Projects which have at least one income and/or expence will be listed only.

Upvotes: 1

Related Questions