Untitled

select  distinct
                campaign_name,
                COUNT(case when EVENT_TYPE = 'READY' then LOGIN_ID else null end)  as nb_ready,
                COUNT(case when EVENT_TYPE = 'NOT_READY' then LOGIN_ID else null end)  as nb_not_ready
from    dbo.ALT_RTR_AgentState ast inner join ALT_RTR_Campaign_Parameters p on (ast.CAMPAIGN_ID = p.campaign_id)
                                   inner join ALT_RTR_Team_Campaign tc on (tc.campaign_id = p.campaign_id)
where   campaign_name in ('MGT_TSP', 'STORE_AS', 'STORE_FLR', 'STORE_TSP')
group by campaign_name
;