CODEWITHSUNDEEP
mysqlsql
StudioTime
StudioTime

Reputation: 24019

Select on aggregate result

I have a query which does a count from another table then adds a column for the result. I then need to alter the original select results based on that but am being told unknown column.

E.g. the following query does a count from another table within the main query, and the result is named shares, I need to filter the main query result set based on whether that column is greater than 0 but I get error unknown column shares

select b.name, event_title, ue.event_vis, event_date, 
    (select count(*) from list_shares 
        where user_id = 63 and event_id=ue.user_event_id) as shares,
    (DAYOFYEAR(ue.event_date) - DAYOFYEAR(CURDATE())) as days 
FROM brains b
    join user_events ue on b.user_id=ue.user_id
where b.user_id=63 and ((ue.event_vis='Public') OR (shares>0)) 
    and MOD(DAYOFYEAR(ue.event_date) - DAYOFYEAR(CURDATE()) + 365, 365) <= 30 
order by days asc

Is there a way to do this?

Upvotes: 0

Views: 56

Answers (2)

timo.rieber
timo.rieber

Reputation: 3867

I would suggest using a derived table to deliver the aggregate value and join it as you would do with a "phyiscal" table. Example:

select
    b.name,
    ue.event_title,
    ue.event_vis,
    ue.event_date, 
    tmp.shares,
    (DAYOFYEAR(ue.event_date) - DAYOFYEAR(CURDATE())) as days

from
    brains b join user_events ue on b.user_id = ue.user_id
    left join (
        select
            ls.user_id,
            ls.event_id,
            count(*) as shares
        from
            list_shares ls
        group by
            ls.user_id,
            ls.event_id) tmp on b.user_id = tmp.user_id and ue.user_event_id = tmp.event_id

where
    b.user_id = 63
and
    ((ue.event_vis = 'Public') OR (tmp.shares > 0))
and 
    MOD(DAYOFYEAR(ue.event_date) - DAYOFYEAR(CURDATE()) + 365, 365) <= 30 

order by
    days asc

Please note the "left join". Because your using the OR operator in your where clause it seems to me like you want to get also rows without a share.

Of course you could also use the same subselect in your where clause but that's duplicate code and harder to maintain.

Upvotes: 1

user2989408
user2989408

Reputation: 3137

You cannot use a computed column to filter in the same query. Try something like 

SELECT x.*
FROM (
    /*Your Query*/
) as x
WHERE x.shares > 0

Or you could do something like 

select b.name, event_title, ue.event_vis, event_date, 
    shares.SharesCount as shares,
    (DAYOFYEAR(ue.event_date) - DAYOFYEAR(CURDATE())) as days 
FROM brains b
    join user_events ue on b.user_id=ue.user_id, event_id 
    JOIN (select count(*) as [sharesCount],  from list_shares 
        where user_id = 63) as shares ON shares.event_id = ue.user_event_id
where b.user_id=63 and ((ue.event_vis='Public') OR (shares>0)) 
    and MOD(DAYOFYEAR(ue.event_date) - DAYOFYEAR(CURDATE()) + 365, 365) <= 30
    AND shares.sharesCount > 0
order by days asc

Upvotes: 0

Related Questions