Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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. 
                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, 2016 at 15:38
                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 for more info.
– Dominic Comtois
                May 24, 2016 at 15:48
                I can't reuse my formula because it says that aggregate functions aren't allowed in the WHERE clause
– DBE7
                May 24, 2016 at 15:51
               Min(Date_part('year', created_at)) AS first_rental_year
        FROM   bookings 
        WHERE  state IN ( 'approved', 'aboard', 'ashore', 'concluded', 'disputed' ) 
WHERE first_rental_year = 2013  
GROUP  BY renter_id
ORDER  BY renter_id ; 
                This looks correct all it needs is T.first_rental_year, T.renter?? Right Juan or am i full of shi?
– FirebladeDan
                May 24, 2016 at 15:44
                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, 2016 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, 2016 at 15:49
SQL Error [42703]: ERROR: column XYZ does not exist
Check you have double quotes around Column Fields:
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"
                @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, 2022 at 22:30
                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: "If you want to write portable applications you are advised to always quote a particular name or never quote it".
– eis
                Jan 21, 2022 at 7:25
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.