Postgresql – Postgres INNER JOIN same table failing on CartoDB

cartodbjoin;postgresql

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?

Best Answer

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.

Related Solutions

Inner Join Performance

Try something like:

select AccountNumber, SnapshotTime, Ticket, ...
from (
    select AccountNumber, SnapshotTime, Ticket, ...
         , row_number() over (partition by AccountNumber, Ticket
                              order by SnapshotTime desc) as rn
    from OpenTrades
) T
where rn = 1;

I just reread the post and realized that I gave a solution for something different than the OP asked for, I guess something like:

select AccountNumber, SnapshotTime, Ticket, ...
from (
    select AccountNumber, SnapshotTime, Ticket, ...
         , row_number() over (to_date(SnapshotTime,'IW')
                              order by SnapshotTime desc) as rn
    from OpenTrades
) T
where rn = 1;

is closer to what the OP wants.

How toNNER JOIN and OUTER JOIN within the same table

The query is joining 2 tables (receipts, sales) that both have a many-to-one relationship with product. This creates a kind of cartesian (cross) product and will give wrong results in the SUM() calculations. To avoid that, you need to do the summations for the two tables in two different subqueries to avoid errors.

Something like this will work:

SELECT 
    m.month, 
    p.name, 
    COALESCE(r.receipts_quantity, 0) AS receipts_quantity, 
    COALESCE(s.sales_quantity, 0)    AS sales_quantity
FROM 
    product AS p
  CROSS JOIN
    ( VALUES (1),(2),(3),(4),(5),(6) ) AS m(month)     -- months wanted in the report
  LEFT OUTER JOIN 
    ( SELECT month, product_id, SUM(quantity) AS receipts_quantity
      FROM receipts
      GROUP BY month, product_id
    ) AS r
    ON  p.id_product = r.product_id
    AND m.month = r.month
  LEFT OUTER JOIN  
    ( SELECT month, product_id, SUM(quantity) AS sales_quantity
      FROM sales
      GROUP BY month, product_id
    ) AS s
    ON  p.id_product = s.product_id
    AND m.month = s.month ;

If you want to avoid having results for products that did not have any activity in a month, you can add the condition:

WHERE r.month IS NOT NULL 
   OR s.month IS NOT NULL

Best Answer

Related Solutions

Inner Join Performance

How toNNER JOIN and OUTER JOIN within the same table

Related Question