Using sub-query column in where clause This question has been asked several times and even answered as well but it's not helpful to me, that's why I am posting it again. I have this query:

select u.*,l.*, 
(
    select count(cr.id) 
    from cloud_recordings cr 
    left join cameras c 
        on c.owner_id=u.id 
    where c.id=cr.camera_id
) valid_licence
from users u 
left join licences l 
    on l.user_id=u.id;


It works fine, but when I put a where clause at the end:

select u.*,l.*, 
(
    select count(cr.id) 
    from cloud_recordings cr 
    left join cameras c 
        on c.owner_id=u.id 
    where c.id=cr.camera_id
) valid_licence
from users u 
left join licences l 
    on l.user_id=u.id
where valid_licence &gt; 0;


It simply gave me an error:


  ERROR:  column "valid_licence" does not exist
  LINE 4: left join licences l on l.user_id=u.id where valid_licence >..
You can't reference an alias on the same "level" where it was defined. You need to wrap your base query into a derived table:

select *
from (
  select u.*,
         l.*, 
         (select count(cr.id) from cloud_recordings cr left join cameras c on c.owner_id=u.id where c.id=cr.camera_id) valid_licence
  from users u 
    left join licences l on l.user_id=u.id 
) t
where valid_licence &gt; 0;