joining two select queries in postgresql

Question

This my first select query

SELECT date_trunc('hour',start_time) as time , extract(day from start_time) as day ,start_zone_id as zone_id, count(*) as booking_trips
  FROM public.trip_information_september where trip_type='{BOOKING}' group by date_trunc('hour',start_time ), start_zone_id ,extract(day from start_time) ORDER BY time,start_zone_id

This is my second select query

  SELECT date_trunc('hour',start_time) as time , extract(day from start_time) as day ,start_zone_id as zone_id, count(*) as booking_trips
      FROM public.trip_information_september where trip_type='{NO_INFO}' group by date_trunc('hour',start_time ), start_zone_id ,extract(day from start_time) ORDER BY time,start_zone_id

So when I join these two

select * from
  (SELECT date_trunc('hour',start_time) as time , extract(day from start_time) as day ,start_zone_id as zone_id, count(*) as booking_trips
  FROM public.trip_information_september where trip_type='{BOOKING}' group by date_trunc('hour',start_time ), start_zone_id ,extract(day from start_time) ORDER BY time,start_zone_id )A
  INNER JOIN
  (SELECT date_trunc('hour',start_time ) as time,extract(day from start_time) as day, start_zone_id as zone_id, count(*) as normal_trips
  FROM public.trip_information_september where trip_type='{NORMAL}' group by date_trunc('hour',start_time ), start_zone_id ,extract(day from start_time) ORDER BY time,start_zone_id) B

  ON A.time=B.time and A.zone_id=B.zone_id and A.day=B.day limit 100 ;

I get the time, day, zone_id twice instead of the joined one. Any help is appreciated.

e4c5 · Accepted Answer · 2016-12-15 05:20:09Z

Which is not very surprisingly since your subquery joins are wrapped in an outer query which does SELECT * you want something like

SELECT A.time, A.day, A.zone_id, booking_trips /*, ... REST OF THE REQUIRED COLUMNS */ FROM

(SELECT date_trunc('hour',start_time) as time , extract(day from start_time) as day ,start_zone_id as zone_id, count(*) as booking_trips
  FROM public.trip_information_september where trip_type='{BOOKING}' group by date_trunc('hour',start_time ), start_zone_id ,extract(day from start_time) ORDER BY time,start_zone_id )A
  INNER JOIN
  (SELECT date_trunc('hour',start_time ) as time,extract(day from start_time) as day, start_zone_id as zone_id, count(*) as normal_trips
  FROM public.trip_information_september where trip_type='{NORMAL}' group by date_trunc('hour',start_time ), start_zone_id ,extract(day from start_time) ORDER BY time,start_zone_id) B

  ON A.time=B.time and A.zone_id=B.zone_id and A.day=B.day limit 100 ;

Collectives™ on Stack Overflow

joining two select queries in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related