sql - PostgreSQL-错误 : column does not exist SQL state: 42703

Question

我正在尝试进行同期群分析，并根据承租人的第一个租赁年(= 承租人首次出租的年份)比较平均租金数。基本上，我在问一个问题:与第一年为 2015 年的租房者相比，我们是否保留了第一年租房为 2013 年的租房者？

这是我的代码:

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; 

我得到的错误信息是:

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

非常感谢任何帮助。

Answer 1

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 ;