CODEWITHSUNDEEP
sqlpostgresql
Anand Srinivasan
Anand Srinivasan

Reputation: 470

JOIN two tables in PostgreSQL and GROUP BY

I have the following tables in PostgreSQL :

table1:

MARKET        character varying 10
COST1         Number

MARKET       COST1      
A             3         
A             7
B             10

table2:

MARKET        character varying"    10
COST2         Number

MARKET       COST2      
A             12         
A             13
B             15
B             15

I am trying to generate a report that will display both cost1 and cost1 of each market. Something like this.

MARKET       COST1      COST2
A             10         25
B             10         30

I executed the following query :

select table1."MARKET", sum(table1."COST1"), sum(table2."COST2") 
from table1  inner join table2 on table1."MARKET" = table2."MARKET" GROUP 
BY(table1."MARKET")

My OP is the following. The cost values are more than what it should be:

MARKET       COST1      COST2
A             20         50
B             20         50

What am I doing wrong? Any help is appreciated!

Upvotes: 1

Views: 71

Answers (2)

Littlefoot
Littlefoot

Reputation: 142710

Alternatively (you need code that begins at line 13; CTE is here so that I could use some data; disregard Oracle-stylish example).

SQL> with table1 (market, cost1) as
  2    (select 'a', 3 from dual union all
  3     select 'a', 7 from dual union all
  4     select 'b', 10 from dual
  5    ),
  6  table2 (market, cost2) as
  7    (select 'a', 12 from dual union all
  8     select 'a', 13 from dual union all
  9     select 'b', 15 from dual union all
 10     select 'b', 15 from dual
 11    )
 12  -- you need what follows:
 13  select x.market, sum(x.cost1) as cost1, sum(y.cost2) as cost2
 14  from (select a.market, sum(a.cost1) cost1
 15        from table1 a
 16        group by a.market) x
 17    join
 18       (select b.market, sum(b.cost2) cost2
 19        from table2 b
 20        group by b.market) y
 21    on x.market = y.market
 22  group by x.market;

M      COST1      COST2
- ---------- ----------
a         10         25
b         10         30

SQL>

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Do the aggregation before the join. Or, use union all and group by:

select market, sum(cost1) as cost1, sum(cost2) as cost2
from ((select market, cost1, 0 as cost2
       from table1
      ) union all
      (select market, 0, cost2
       from table2
      )
     ) mc
group by market;

Upvotes: 1

Related Questions