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
– Erwin Brandstetter, Commented Jun 21, 2017 at 12:42

Erwin Brandstetter · Accepted Answer · 2017-06-21 13:13:19Z

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: That's fixed by now. Try the latest version. (month is an output column name, so the table-qualification was wrong.)

Fabrizio Mazzoni · Accepted Answer · 2017-06-21 12:19:54Z

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.

Collectives™ on Stack Overflow

PostgreSQL crosstab() alternative with CASE and aggregates

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related