mysql - Select on aggregate result -
i have query count table adds column result. need alter original select results based on being told unknown column.
e.g. following query count table within main query, , result named shares, need filter main query result set based on whether column greater 0 error unknown column shares
select b.name, event_title, ue.event_vis, event_date, (select count(*) list_shares user_id = 63 , event_id=ue.user_event_id) shares, (dayofyear(ue.event_date) - dayofyear(curdate())) days brains b join user_events ue on b.user_id=ue.user_id b.user_id=63 , ((ue.event_vis='public') or (shares>0)) , mod(dayofyear(ue.event_date) - dayofyear(curdate()) + 365, 365) <= 30 order days asc
is there way this?
i suggest using derived table deliver aggregate value , join "phyiscal" table. example:
select b.name, ue.event_title, ue.event_vis, ue.event_date, tmp.shares, (dayofyear(ue.event_date) - dayofyear(curdate())) days brains b join user_events ue on b.user_id = ue.user_id left join ( select ls.user_id, ls.event_id, count(*) shares list_shares ls group ls.user_id, ls.event_id) tmp on b.user_id = tmp.user_id , ue.user_event_id = tmp.event_id b.user_id = 63 , ((ue.event_vis = 'public') or (tmp.shares > 0)) , mod(dayofyear(ue.event_date) - dayofyear(curdate()) + 365, 365) <= 30 order days asc
please note "left join". because using or operator in clause seems me want rows without share.
of course use same subselect in clause that's duplicate code , harder maintain.
Comments
Post a Comment