Uniting two statements in SQL (using JOIN or more efficient way)-CodePudding

I made two statements - SUM of revenue and expenses in different tables using group by id (each id has more than one revenue and expenses records):

select id_person, SUM (revenue_d) as sum_revenue from sales group by id_person;

select id_person, SUM (expenses_d) as sum_expenses from expenses group by id_person;

Now I need to subtract expenses.sum_expenses from sales.sum_revenue.

What is a more efficient way to do so?

Using JOINs? Something like that:

SELECT id_person, (sales.sum_revenue - expenses.sum_expenses) as balance FROM sales LEFT OUTER JOIN expenses ON sales.id_person = expenses.id_person;

But how to write all three statements in one?

SELECT id_person, (sales.sum_revenue - expenses.sum_expenses) as balance 
FROM (select id_person, SUM (revenue_d) as sum_revenue from sales group by id_person)
LEFT OUTER JOIN 
(select id_person, SUM (expenses_d) as sum_expenses from expenses group by id_person) 
ON sales.id_person = expenses.id_person;`

CodePudding user response：

There is no need for subqueries.

Assuming the sales has at least as many person_ids as expenses

You can do

SELECT 
    s.id_person,
    SUM (s.revenue_d) - SUM (COALESCE(e.expenses_d,0))   AS balance
FROM
    sales s
        LEFT OUTER JOIN
    expenses e ON s.id_person = e.id_person
GROUP BY s.id_person;

CodePudding user response：

First UNION ALL, then GROUP BY:

select id_person, sum(revenue_d) as sum_revenue, sum(expenses_d) as sum_expenses 
from
(
    select id_person, revenue_d, null as expenses_d from sales
    union all
    select id_person, null, expenses_d from expenses
) dt
group by id_person;

Or, if you also have a persons table:

select p.id_person, sum(s.revenue_d) as sum_revenue, sum(e.expenses_d) as sum_expenses 
from persons p
left join sales s on p.id_person = s.id_person
left join expenses e on p.id_person = e.id_person
group by p.id_person