How to sum two tables with same columns?

I am using postgresql database and have 2 tables. They have same type columns.

id      sale     material
1       10       m1
2       2        m2
3       3        m1
4       40       m3
5       50       m2

id      name     material
1       5        m3
2       15       m1
3       20       m1
4       20       m3
5       10       m1

So I want to get a joined sql like:

material      table1_sale       table2_sale
m1            13                45
m2            52                0
m3            40                25

using query

select t1.material , sum(t1.sale), sum(t2.sale)
from table1 t1 
join table2 t2 on t1.material = t2.material
group by t1.material, t2.material

But gets wrong result. How can I do this?


  • You current query is summing wrong results because it's summing the values from this query:

    select t1.material , t1.sale, t2.sale
    from table1 t1 
    join table2 t2 on t1.material = t2.material

    with some steps, show in the dbfiddle, this is the final query:

    SELECT material, SUM(table1_sale), SUM(table2_Sale)
    from (
       select material, sum(sale) as table1_sale, 0 as table2_sale from table1 group by material
       union all
       select material, 0, sum(sale) from table2 group by material
      ) x
    group by material
    order by material;

    see: DBFIDDLE