How to sum two tables with same columns?-CodePudding

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

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

Table2
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?

CodePudding user response：

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

CodePudding user response：

A possible approach is to aggregate sale by material before joining.

With A As
(Select material, Sum(sale) As table1_sale From table1 Group by material),
B As
(Select material, Sum(sale) As table2_sale From table2 Group by material)
Select Coalesce(t1.material,t2.material) As material, 
       Coalesce(t1.table1_sale,0) As table1_sale, 
       Coalesce(t2.table2_sale,0) As table2_sale
From A As t1 Full Join B As t2 On (t1.material=t2.material)
Order by material

Output:

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

db<>fiddle

CodePudding user response：

Yes, group before joining.

I did it with the WITH clause, as I prefer it for readability ...

WITH
-- your input, don't use in final query ..
table1(id,sale,material) AS (
          SELECT 1,10,'m1'
UNION ALL SELECT 2,2,'m2'
UNION ALL SELECT 3,3,'m1'
UNION ALL SELECT 4,40,'m3'
UNION ALL SELECT 5,50,'m2'
)
,
table2(id,sale,material) AS (
          SELECT 1,5,'m3'
UNION ALL SELECT 2,15,'m1'
UNION ALL SELECT 3,20,'m1'
UNION ALL SELECT 4,20,'m3'
UNION ALL SELECT 5,10,'m1'
)
-- end of your input, replace following comma with "WITH" 
,
t1grp AS (
  SELECT
    material
  , SUM(sale) AS t1sale
  FROM table1
  GROUP BY material
)
,
t2grp AS (
  SELECT
    material
  , SUM(sale) AS t2sale
  FROM table2
  GROUP BY material
)
SELECT
  t1grp.material
, t1sale
, NVL(t2sale,0)
FROM t1grp LEFT JOIN t2grp USING(material)
ORDER BY 1;
-- out  material | t1sale | NVL 
-- out ---------- -------- -----
-- out  m1       |     13 |  45
-- out  m2       |     52 |   0
-- out  m3       |     40 |  25