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 > 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 > 0;

StackExchange Database Administrators Q#132182, answer score: 8