6

I am trying to do a cohort analysis and compare average number of rentals based on the renter's first rental year(= the year where a renter rented first time). Basically, I am asking the question: are we retaining renters whose first year renting was 2013 than renters whose first year was 2015?

Here is my code:

SELECT renter_id, 
       Min(Date_part('year', created_at)) AS first_rental_year, 
       ( Count(trip_finish) )             AS number_of_trips 
FROM   bookings 
WHERE  state IN ( 'approved', 'aboard', 'ashore', 'concluded', 'disputed' ) 
  AND  first_rental_year = 2013 
GROUP  BY 1 
ORDER  BY 1;

The error message I get is:

ERROR:  column "first_rental_year" does not exist
LINE 6: ... 'aboard', 'ashore', 'concluded', 'disputed') AND first_rent...
                                                             ^

********** Error **********

ERROR: column "first_rental_year" does not exist
SQL state: 42703
Character: 208

Any help is much appreciated.

DBE7
  • 766
  • 2
  • 9
  • 23
  • 2
    i dont see the error part on your query you posted... the correct syntax should be `AND extract(year from b1.created_at)` – cableload May 24 '16 at 15:38
  • 1
    Are you sure this is the same query? Error message is saying LINE 10? – FirebladeDan May 24 '16 at 15:39
  • 1
    Sorry guys, added the correct error message – DBE7 May 24 '16 at 15:39
  • 1
    The problem is that the column `first_rental_year` is not in your source table. Other SQL engines support syntax such as `AND CALCULATED first_rental_year = 2013`, but postgre doesn't. You could reuse your formula (`AND Min(Date_part('year', created_at)) = 2013`) or better, use @Juan Carlos' solution which avoids this redundancy. See [this post](http://stackoverflow.com/questions/8250389/computed-calculated-columns-in-postgresql) for more info. – Dominic Comtois May 24 '16 at 15:48
  • 1
    I can't reuse my formula because it says that aggregate functions aren't allowed in the WHERE clause – DBE7 May 24 '16 at 15:51
  • i'd belong in the having clause but i'd go with @Juan Carlos – A ツ May 24 '16 at 15:53

2 Answers2

6
SELECT renter_id,
       Count(trip_finish) AS number_of_trips 
FROM (
        SELECT renter_id, 
               trip_finish,
               Min(Date_part('year', created_at)) AS first_rental_year
        FROM   bookings 
        WHERE  state IN ( 'approved', 'aboard', 'ashore', 'concluded', 'disputed' ) 
     ) T
WHERE first_rental_year = 2013  
GROUP  BY renter_id
ORDER  BY renter_id ;
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • This looks correct all it needs is T.first_rental_year, T.renter?? Right Juan or am i full of shi? – FirebladeDan May 24 '16 at 15:44
  • 2
    The problem is you cant use the alias `first_rental_year` on the where, because at that time the alias doesnt exist. So you create the alias inside a subquery and use it outside. You also can change the alias and use the function `Min(Date_part('year', created_at)) = 2013` – Juan Carlos Oropeza May 24 '16 at 15:46
  • ERROR: column "t.first_rental_year" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT * ^ ********** Error ********** ERROR: column "t.first_rental_year" must appear in the GROUP BY clause or be used in an aggregate function SQL state: 42803 Character: 8 – DBE7 May 24 '16 at 15:49
1

ERROR:

SQL Error [42703]: ERROR: column XYZ does not exist

Check you have double quotes around Column Fields:

BAD:

update public."AppTime" t Set "CustomTask"= 'XYZ' where  t.SharedAppId = 12890;

GOOD:

With double quotes around "SharedAppId"

update public."AppTime" t Set "CustomTask"= 'XYZ' where  t."SharedAppId" = 12890;

If you created the table without quotes, you should not use quotes when querying it, and vice versa. This is explained in the manual: "If you want to write portable applications you are advised to always quote a particular name or never quote it"

Jeremy Thompson
  • 61,933
  • 36
  • 195
  • 321
  • not really relevant for this question – eis Jan 20 '22 at 16:23
  • @eis completely relevant, it produces the same error message and this is the SO thread for answers. Disappointing you've been here longer than me and don't appreciate people sharing knowledge :( – Jeremy Thompson Jan 20 '22 at 22:30
  • 1
    1) even if it would produce the same error, in this thread the problem was different, and 2) The answer is ill-advised. If you want to go through the route of using quotes, you really should explain it all - when to quote and when to not quote. If you created the table without quotes, you should *not* use quotes when querying it, and vice versa. This is [explained in the manual](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS): "If you want to write portable applications you are advised to always quote a particular name or never quote it". – eis Jan 21 '22 at 07:25