从另一个表中获取 2 个不同表的总和 [英] Getting SUM of 2 different table from another table

问题描述

我正在研究费用/预算跟踪系统，但在某些方面遇到困难.
我有4张桌子
tblProjects

tblCategory

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

tblCategory

tbl费用

tblIncomes

tblProjects 以 1:1 与 tblCategory 相关，

tblProjects relate to tblCategory with 1:1,

tblExpenses 与 tblProjects 也有 1:1 的关系，

tblExpenses relate to tblProjects also with 1:1,

与 1:1 的 tblProjects 相关的 tblIncomes 也是如此

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

我正在尝试按年份获取每个类别组的支出和收入总和(来自 tblProjects.proj_sdate 列)，以及一列将显示损益表(费用 - 收入).
例如，我想知道支出总额、收入总额、旅行、运动等花费的损益价值是多少
下面是我想要实现的示例；

我设法在下面提出了 2 个将收入和支出分开的查询.但不确定如何组合它使其成为单个查询并执行减法以获取 P&L 值.

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;

I managed to come up with 2 query below which separate the incomes and expenses. But not sure how to combine it making it single query and perform the subtraction as well to get the P&L value.

1)Incomes
SELECT category.cat_title as Category, group_concat(distinct 
projects.proj_title) as Projects, date_format(projects.proj_sdate, '%Y') as 
Year, sum(incomes.inc_amount) as Total_Incomes from category inner join 
projects on projects.proj_cat = category.cat_id inner join incomes on 
incomes.projects_id = projects.proj_id group by category.cat_title, 
date_format(projects.proj_sdate, '%Y')

2)Expenses
SELECT category.cat_title as Category, group_concat(distinct 
projects.proj_title) as Projects, date_format(projects.proj_sdate, '%Y') as 
Year, sum(expenses.exp_amount) as Total_Expenses from category inner join 
projects on projects.proj_cat = category.cat_id inner join expenses on 
expenses.projects_id = projects.proj_id group by category.cat_title, 
date_format(projects.proj_sdate, '%Y')

推荐答案

我认为这个查询会给你想要的结果.因为每个项目可以有多个费用和收入线，所以您需要 JOIN 到收入和费用汇总表(按项目)而不是原始表.然后将所有 JOIN 的结果按 Category 和 Year 分组:

I think this query will give you the results you want. Because you can have multiple expense and income lines per project, you need to JOIN to summed tables of incomes and expenses (by project) rather than the raw table. The results of all the JOINs are then grouped by Category and Year:

SELECT c.cat_title AS Category,
       GROUP_CONCAT(p.proj_title) AS Projects,
       YEAR(p.proj_sdate) AS Year,
       COALESCE(SUM(i.income), 0) AS Total_Income,
       COALESCE(SUM(e.expenses), 0) AS Total_Expenses,
       COALESCE(SUM(i.income), 0) - COALESCE(SUM(e.expenses), 0) AS `P&L`
FROM tblCategory c
JOIN tblProjects p ON p.proj_cat = c.cat_id
LEFT JOIN (SELECT projects_id, SUM(exp_amount) AS expenses
           FROM tblExpenses 
           GROUP BY projects_id) e ON e.projects_id = p.proj_id
LEFT JOIN  (SELECT projects_id, SUM(inc_amount) AS income
           FROM tblIncome
           GROUP BY projects_id) i ON i.projects_id = p.proj_id
GROUP BY Category, Year

结果(针对您问题中的示例数据):

Results (for the sample data in your question):

Category    Projects                    Year    Total_Income    Total_Expenses  P&L
KSS Talks   Projects XYZ                2017    2.00            152.00          -150.00
KSS Talks   Projects X,Projects Satu    2018    371.00          365.00          6.00
Sports      Projects Y                  2018    150.00          0.00            150.00
Trip        Projects Z                  2018    15.00           0.00            15.00

dbfiddle 演示

这篇关于从另一个表中获取 2 个不同表的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！