CODEWITHSUNDEEP
sqlpostgresql
smith
smith

Reputation: 113

Is there a way to get the following result in Postgresql

so there are 2 tables.

Table1

   month | orders
    Jan  |  10
    Feb  |  4
    Apr  |  7

Table2

   month | dispatch
    Jan  | 2
    May  | 3

I want to get the following result:-

   month | orders | dispatch
    Jan  |   10   |   2
    Feb  |   4    |   0
    Apr  |   7    |   0
    May  |   0    |   3

What all ways are there to approach this?

Upvotes: 0

Views: 43

Answers (2)

ferhatelmas
ferhatelmas

Reputation: 3978

Full outer join for all combinations and coalesce for defaults as follows:

 CREATE TABLE table1 (month text, orders int); 
 CREATE TABLE table2 (month text, dispatch int);
 INSERT INTO table2 (month, orders) values ('Jan', 10), ('Feb', 4), ('Apr', 7);
 INSERT INTO table2 (month, dispatch) VALUES ('Jan', 2), ('May', 3);

 SELECT month, COALESCE(dispatch, 0), COALESCE(orders, 0)
 FROM table1 FULL JOIN table2 USING (month)
 ORDER BY EXTRACT(month FROM TO_DATE(month, 'Mon'))

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

use full outer join

  select coalesce(t1.month,t2.month),coalesce(t1.orders,0)
 ,coalesce(t2.dispatch,0)
  from table1 t1 full outer join table2 t2 on t1.month=t2.month

Upvotes: 4

Related Questions