Complex sql query with inner and left join with having and EXTRACT [closed]

Question

Im getting this error from my query:

ERROR: missing FROM-clause entry for table "cc" LINE 2: ... tt.sourceupdated_at having EXTRACT(DAY FROM max(cc.source_... ^

I'm stuck with this, please help me.

select count(*) AS full_count, (array_agg(id))[1:5] from (
    select distinct tt.id, tt.sourceupdated_at from (
        select t.id as id, t.sourceupdated_at from tickets_ticketagent as ta 
            inner join tickets_ticket as t on t.reference_id = ta.ticket_id 
            inner join users_agent as ua on ua.reference_id = ta.agent_id and ua.status = 1 
            inner join users_user as u on u.id = ua.user_id and u.status = 1  
            left join tickets_cleancomments as cc on cc.ticket_id = t.reference_id and cc.status = 1  
                where t.status in (1,2) and ta.agent_id = ANY( ARRAY[398290723973, 404958111713]::bigint[] )  
                group by t.id having count(distinct cc.comment_id) <= 6
    ) as tt 
    group by tt.id, tt.sourceupdated_at  
    having EXTRACT(DAY FROM max(cc.source_created_at) - min(cc.source_created_at)) > 10 
    order by tt.sourceupdated_at desc 
) as a

Answer 1

I think you are using PostgreSQL instead of MySQL! because you have many PostgreSQL functions in your query.
So I create a sample fiddle based on your query with some modifications here that works fine.
I think you can use it.

Now explain some tips about your query:

1. you have an error in line 9:
   because you don't have t.sourceupdated_at after group by.
2. when you want to add a field in outer selects you need to add them in inner select statement. So, first add source_created_at in inner select (+ after group by) like this.
3. And don't forget that now you should use tt as alias of table instead of cc.

final query:

select count(*) AS full_count, (array_agg(id))[1:5] from (
    select distinct tt.id, tt.sourceupdated_at from (
        select t.id as id, t.sourceupdated_at, cc.source_created_at from tickets_ticketagent as ta 
            inner join tickets_ticket as t on t.reference_id = ta.ticket_id 
            inner join users_agent as ua on ua.reference_id = ta.agent_id and ua.status = 1 
            inner join users_user as u on u.id = ua.user_id and u.status = 1  
            left join tickets_cleancomments as cc on cc.ticket_id = t.reference_id and cc.status = 1  
                where t.status in (1,2) and ta.agent_id = ANY( ARRAY[398290723973, 404958111713]::bigint[] )  
                group by t.id, t.sourceupdated_at, cc.source_created_at
                having count(distinct cc.comment_id) <= 6
    ) as tt 
    group by tt.id, tt.sourceupdated_at  
    having EXTRACT(DAY FROM max(tt.source_created_at) - min(tt.source_created_at)) > 10
    order by tt.sourceupdated_at desc 
) as a