Accessing parent query variables in sub query

Question

I have a mysql query that look likes this

select 
    s.id, sum(history.virality)
from
    spreads s , (
                select 
                     ( ( (sum(t.likes)) + ( sum(t.comments)) ) * ( 1 / ( 1 +  s.created_at  / 3600000 ) ) )as response
                from
                    spreadstimeline t
                where
                    t.spread_id = s.id
                group by
                    DATE_FORMAT(t.created_at,'%Y-%m-%d %H')
            ) as history
group by
    s.id

Mysql return an error "Unknown column 's.created_at' in 'field list'"

is there a possible way to get s to appear in the subquery, or should i start thinking of a different query.

000 · Accepted Answer · 2013-09-10 15:37:59Z

1

Perhaps something along these lines:

select 
    s.id, sum_virality, ( ( (sum_likes) + ( sum_comments) ) * ( 1 / ( 1 +  s.created_at  / 3600000 ) ) )as response
from spreads s
inner join (select t.spread_id, DATE_FORMAT(t.created_at,'%Y-%m-%d %H') created_date, sum(t.virality) sum_virality, sum(t.likes) sum_likes, sum(t.comments) sum_comments
            from spreadstimeline t
            where t.spread_id = s.id
            group by created_date) as history
group by
    s.id

answered Sep 10, 2013 at 15:37

000

27.3k10 gold badges74 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Accessing parent query variables in sub query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related