0

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?

1
  • Well, it sounds like this is specific to CartoDB, so without access to that product, I doubt it. Jun 16 '15 at 0:48
0

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.

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.