SELECT
p.*, concat(p.fname,' ',p.lname,' ',p.piradi_nomeri) as person, group_concat(distinct pp.id) as all_ids,
ap.id as active_player_id, ap.team_id as active_team_id, at.name as active_team_name,
t.name as team_name,
st.id as tournament_id, st.subject as tournament_name, st.color,
count(distinct eg.id) as goals,
count(distinct eg7.id) as goals7,
count(distinct eg10.id) as goals10,
count(distinct a.id) as assists,
count(distinct es7.id) as saves7,
count(distinct es10.id) as saves10,
count(distinct ey.id) as yellow,
count(distinct er.id) as red,
count(distinct er2.id) as red2,
count(distinct eo.id) as owngoal,
count(distinct gp.id) as pgames
FROM players as pp
left join players as p on p.lname=pp.lname and p.fname=pp.fname and p.piradi_nomeri=pp.piradi_nomeri
left join teams as t on t.id=pp.team_id
left join seasons_tournaments as st on st.season_id='12'
left join events as eg on eg.player_id=pp.id and eg.event_type like 'Goal%' and eg.tournament_id=st.id and eg.event_period!='penalties'
left join events as eg7 on eg7.player_id=pp.id and eg7.event_type='Goal 7m' and eg7.tournament_id=st.id and eg7.event_period!='penalties'
left join events as eg10 on eg10.player_id=pp.id and eg10.event_type='Goal 10m' and eg10.tournament_id=st.id and eg10.event_period!='penalties'
left join events as a on a.assistant_id=pp.id and a.event_type like 'Goal%' and a.tournament_id=st.id
left join events as es7 on es7.player_id=pp.id and es7.event_type='Save 7m' and es7.tournament_id=st.id
left join events as es10 on es10.player_id=pp.id and es10.event_type='save 10m' and es10.tournament_id=st.id
left join events as ey on ey.player_id=pp.id and ey.event_type='Yellow Card' and ey.tournament_id=st.id
left join events as er on er.player_id=pp.id and er.event_type like 'Red%' and er.tournament_id=st.id
left join events as er2 on er2.player_id=pp.id and er2.event_type like 'Red(y2%' and er2.tournament_id=st.id
left join events as eo on eo.player_id=pp.id and eo.event_type='Own Goal' and eo.tournament_id=st.id
left join games as gm
on ( gm.id=eg.game_id
or gm.id=eg7.game_id
or gm.id=eg10.game_id
or gm.id=a.game_id
or gm.id=es7.game_id
or gm.id=es10.game_id
or gm.id=ey.game_id
or gm.id=er.game_id
or gm.id=eo.game_id ) and gm.season_id='12'
left join games as gp on ( gp.actual_team_1_players LIKE CONCAT( '%"',pp.id,'":%' ) or gp.actual_team_2_players LIKE CONCAT( '%"',pp.id,'":%' ) ) and gp.season_id='12'
left join players as ap on ap.fname=p.fname and ap.lname=p.lname and ap.piradi_nomeri=p.piradi_nomeri /*and ap.active='1'*/
left join teams as at on at.id=ap.team_id
where p.id='38567' and st.season_id='12' and pp.team_id!=0
group by
concat(p.fname,' ',p.lname,' ',p.piradi_nomeri)
order by goals desc, assists desc, yellow asc, red asc, owngoal asc, goals7 asc, goals10 asc, st.id asc
