Postgres INNER JOIN same table failing on CartoDB

Question

I have a table with data which looks like this

+----+------+-------+
| cp | year | count |
+----+------+-------+
| 1 | 2000 | 10000 |
| 1 | 2001 | 9000 |
| 1 | 2002 | 9500 |
| 2 | 2000 | 8000 |
| 2 | 2001 | 7500 |
| 2 | 2002 | 7000 |
+----+------+-------+

Every cp is guaranteed to have the same number of years. What I want is the percentage change between the current year and a reference year. So I'm after this:

+----+------+-------+-----------+--------------+
| cp | year | count | since2000 | since2000pct |
+----+------+-------+-----------+--------------+
| 1 | 2000 | 10000 | 0 | 0 |
| 1 | 2001 | 9000 | -1000 | -0.1 |
| 1 | 2002 | 9500 | -500 | -0.05 |
| 2 | 2000 | 20000 | 0 | 0 |
| 2 | 2001 | 16000 | -4000 | -0.2 |
| 2 | 2002 | 21000 | 1000 | 0.05 |
+----+------+-------+-----------+--------------+

It's been awhile since I've done much with SQL, but this looks like a pretty straightforward inner join. All I should need is the year 2000 count repeated for every row, and the rest is just math. I got it working using SQLFiddler:

http://sqlfiddle.com/#!15/81a94/1/0

SELECT
*
FROM traffic_counts as s1
INNER JOIN
(SELECT cp, count AS count2000 FROM traffic_counts WHERE year = 2000) s2
ON s1.cp = s2.cp;

But when I try to run this on CartoDB (PostgreSQL 9.3.4) I'm getting an error saying that cp is ambiguous. (Fiddler has no problem...) I even tried aliasing the table to s3 inside the subselect and fully qualifying the first "cp", but the error was the same.

Can anyone help?

• Craig Ringer: Well, it sounds like this is specific to CartoDB, so without access to that product, I doubt it.

Answer 1

The problem may be that you have multiple columns of the same name in the result (2x cp). When using SELECT * you would return both.

The simple fix for the query at hand would be USING in the join clause, which only keeps one instance of the joined column in the result (and seems smarter in this particular case anyway):

SELECT *
FROM traffic_counts s1
JOIN (SELECT cp, count AS count2000 FROM traffic_counts WHERE year = 2000) s2 USING (cp);

Updated SQL Fiddle.

BTW, SQL Fiddle itself was updated only recently to allow such ambiguity. Until some months ago it also raised an error for queries with duplicate names in the result. Quite a few clients cannot cope with that.

• Nick K9: USING was the SQL command I didn't know. Thanks very much for the super quick and clear explanation!
• Erwin Brandstetter: @NickK9: USING is not exactly a "command", just a clause for join conditions in any FROM list.