PostgreSQL crosstab() alternative with CASE and aggregates

Question

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

Table bookings:

  timestamp
, bookings
, provider_id

Table providers:

  provider_id
, travel_mode

Pivot table function and crosstab functions are not to be used to do this. So I am trying to use JOIN and CASE. Following is the query:

  SELECT b.month, 
  (CASE WHEN p.travel_mode=train then b.amount end)train,
  (CASE WHEN p.travel_mode=bus then b.amount end)bus, 
  (CASE WHEN p.travel_mode=air then b.amount end)air
  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 b.month, p.travel_mode)
  group by b.month;

However I am getting an error which says:

subquery in FROM must have an alias LINE 6:

And when I add an alias it throws an error saying:

column p.travel_mode must appear in the GROUP BY clause or be used in an aggregate function 
LINE 2:

The final result should be something like this

Month       Air             Bus            Train  
01          Amount(air)     Amount(Bus)    Amount(train)

I have a feeling it is a minor error somewhere but I am unable to figure it out at all.

P.S. I had to remove all quotations in the question as it was not allowing me to post this. But those are being taken care of in the actual query.

You can (and should) post all your quotations. SO certainly allows that. You should always add the actual table definition (`CREATE TABLE` statement) showing data types and constraints and your version of Postgres. — Erwin Brandstetter, Jun 21 '17 at 12:42

Erwin Brandstetter · Accepted Answer · 2017-06-21T13:13:19.993

Multiple problems. The missing table alias is just one of them. This query should work:

SELECT month
     , sum(CASE WHEN travel_mode = 'train' THEN amount END) AS train
     , sum(CASE WHEN travel_mode = 'bus'   THEN amount END) AS bus
     , sum(CASE WHEN travel_mode = 'air'   THEN amount END) AS air
FROM  (
   SELECT to_char(timestamp, 'MM') AS month, travel_mode, sum(bookings) AS amount
   FROM   bookings  b
   JOIN   providers p USING (provider_id)
   GROUP  BY month, p.travel_mode
   ) sub
GROUP  BY month;

Missing single quotes for string literals. (You seem to have removed those being under the wrong impression you couldn't post quotations.)
Missing table alias for the subquery - just like the 1st error message says.
In the outer query, table names (or aliases) of underlying tables in the subquery are not visible. Only the table alias of the subquery is. Since there is only one subquery, you don't need table-qualification at all there.
month is an output column name (not in the underlying table), so the table qualification b.month was wrong, too.
You seem to want 2-digit numbers for months. Use the template pattern 'MM' instead of 'month' with to_char().
The aggregation in the outer query does not work like you had it - just like your 2nd error message says. You have to wrap the outer CASE expression in a aggregate function. You might as well use min() or max() in this case, because there are never more than one rows after the subquery.
Still unclear where date_ is coming from? You mean timestamp? (which is not a good identifier).

But you don't need the subquery to begin with and can simplify to:

SELECT to_char(timestamp, 'MM') AS month
     , sum(CASE WHEN p.travel_mode = 'train' THEN b.bookings END) AS train
     , sum(CASE WHEN p.travel_mode = 'bus'   THEN b.bookings END) AS bus
     , sum(CASE WHEN p.travel_mode = 'air'   THEN b.bookings END) AS air
FROM   bookings  b
JOIN   providers p USING (provider_id)
GROUP  BY 1;

For best performance you should still use crosstab(), though:

PostgreSQL Crosstab Query

The 1st query did not work. This is the error it is throwing ERROR: column b.month does not exist LINE 9: GROUP BY b.month, p.travel_mode ^ — justanothernewbie, Jun 21 '17 at 13:03
@justanothernewbie: That's fixed by now. Try the latest version. (`month` is an output column name, so the table-qualification was wrong.) — Erwin Brandstetter, Jun 21 '17 at 13:05

score 0 · Answer 2 · answered Jun 21 '17 at 12:19

You have to name the subquery as the error message says:

SELECT b.month, 
(CASE WHEN p.travel_mode=train then b.amount end)train,
(CASE WHEN p.travel_mode=bus then b.amount end)bus, 
(CASE WHEN p.travel_mode=air then b.amount end)air
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 b.month, p.travel_mode)
**as foo** group by b.month;

Remove the stars to make it work.

PostgreSQL crosstab() alternative with CASE and aggregates

2 Answers2

Linked