0

I want to create a pivot table view showing month on month sum of bookings for every travel_mode.

Table 1 bookings - timestamp, bookings, provider_id Table 2 providers - provider_id, travel_mode

Pivot table function and crosstab functions are not to be used to do this exercise. I want to either use joins or some other function.

I don't understand the issue with this query I am trying to run, it shows "relation x does not exist"

SELECT c.month, train.amount, bus.amount, air.amount
FROM (SELECT x.month
      FROM (SELECT  to_char(date_,'month') as month, travel_mode, sum(bookings) as amount
    from bookings as b
    join providers as p
    on b.provider_id=p.provider_id
    group by month, travel_mode) x
      group by x.month
      ) c
JOIN x train ON c.month = train.month AND train.amount = 'train'
JOIN x bus ON c.month = bus.month AND bus.amount = 'bus'
JOIN x air ON c.month = air.month AND air.amount = 'air'
;
Vivek Amarnani
  • 1
  • Should this be tagged postgresql? – P.Salmon Jun 21 '17 at 08:50
  • Presumably there is no table x and you cannot join to a sub query created in the query (in mysql anyway no idea about postgresql) . If you can provide sample data and expected output I'm sure someone will be able to assist. BTW mysql and Postgresql are different you should only tag for the sql dialect you want a solution for. – P.Salmon Jun 21 '17 at 09:09
  • Edited the tags :) How do I declare the table x so that I can use it for join? – Vivek Amarnani Jun 21 '17 at 09:26

1 Answers1

0

If I dissect your query I think this is what you are trying to do:

with x as (
  SELECT  to_char(date_,'month') as month, travel_mode, sum(bookings) as amount
  from bookings as b
    join providers as p on b.provider_id=p.provider_id
  group by month, travel_mode) x
)
SELECT c.month, train.amount, bus.amount, air.amount
FROM (
    SELECT x.month
    FROM x
    group by x.month
) c  
  JOIN x train ON c.month = train.month AND train.amount = 'train'
  JOIN x bus ON c.month = bus.month AND bus.amount = 'bus'
  JOIN x air ON c.month = air.month AND air.amount = 'air'

If you want to join to the result of a query (derived table) multiple times, you have to put that into a common table expression

But the select month from x group by month part does not make any sense to me.

  • I am trying to follow the query given here: https://stackoverflow.com/questions/7563796/how-to-do-pivoting-in-postgresql/18351884 The only difference is that I have to create the table x which I can use as per the above query. – Vivek Amarnani Jun 21 '17 at 10:02
  • @VivekAmarnani You don't need to create table x - the CTE will do just fine –  Jun 21 '17 at 10:03
  • I think that those should be `LEFT JOIN` unless @VivekAmarnani doesn't want to see amount of train bookings for May 2017 if there were no bus bookings in that month. Also he probably wanted to use `travel_mode = 'train'` in those joins, since amount is most likely some numerical value as it was `sum()` before. But I suppose question was about relation x not existing. – Łukasz Kamiński Jun 21 '17 at 11:52