Question 115071 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Uniting two statements in SQL (using JOIN or more efficient way)

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;`
Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search