select concat(u.first_name, ' ', u.last_name) as name, u.skill, ut.name as team_name,
(count(if(a.tech_id = u.user_id AND a.qc_id = 0 AND a.status = 2,1,0))) AS assigned_scopes,
(count(if(a.tech_id = u.user_id  AND a.qc_id > 0 AND a.status = 3,1,0))) AS assigned_qa,
(count(if(a.tech_id = u.user_id  AND a.qc_id > 0 AND a.status = 3,1,0))) AS failed_qa,
(count(if(a.assignment_id=o.assignment_id and a.tech_id = u.user_id  AND a.status = 2 AND a.qc_id = 0 and o.class_id= 3,1,0))) AS assigned_canvass,
from am_ts_order o left join am_ts_assignment a
ON a.assignment_id=o.assignment_id
left join am_user u
ON a.tech_id=u.user_id
left join user_team ut
ON u.user_team_id = ut.user_team_id
where u.user_role_id = 15
and u.user_team_id = ut.user_team_id
order by u.first_name asc