SELECT (sum(gp)+sum(inj)) as gp, sum(1att) as 1att, sum(1com) as 1com, sum(1yds) as 1yds,  sum(1td) as 1td, sum(1int) as 1int, sum(1sk) as 1sk, coalesce((sum(1com)/sum(1att))*100,0.000) AS 1comp_per, coalesce(sum(1yds)/sum(1com),0.000) AS 1yds_com, 
		CASE WHEN sum(1att) >= (sum(gp)*10) THEN coalesce((sum(1com)/sum(1att))*100,0.000) ELSE 0 END AS 1comp_per_min_att,
		sum(2rec) as 2rec, sum(2yds) as 2yds, sum(2td) as 2td, coalesce(sum(2yds)/sum(2rec),0.000) AS 2yds_rec,	
		sum(3tkl) as 3tkl, sum(3int) as 3int,	sum(3sk) as 3sk, sum(3pd) as 3pd, sum(3td) as 3td,	
		sum(4att) as 4att, sum(4yds) as 4yds, sum(4td) as 4td, coalesce(sum(4yds)/sum(4att),0.000) AS 4yds_rush,
		sum(5ur) as 5ur, sum(5oc) as 5oc,sum(5ur)+sum(5oc) as disciplinary_total,
		member_id, member_lname, member_fname, member_image, pos_name, 
		position_id, player_number, team_id, team_name, league_id, division_id, season_start, season_name, season_id
		
		FROM(
	
			SELECT me.member_image, me.member_id, me.member_lname, me.member_fname,
			spt.position_abbrev as pos_name, spt.position_id, spt.team_id, tp.player_number,
			s.season_id, s.season_name, s.season_start, tp.league_id, tp.division_id, t.team_name,
				sum(CASE WHEN sp.stats_player_code = 'GP' THEN spt.value ELSE 0 END) AS gp,
				sum(CASE WHEN sp.stats_player_code = 'INJ' THEN spt.value ELSE 0 END) AS inj,
				sum(CASE WHEN sp.stats_player_code = '1ATT' THEN spt.value ELSE 0 END)  AS 1att,
				sum(CASE WHEN sp.stats_player_code = '1COM' THEN spt.value ELSE 0 END)  AS 1com,
				sum(CASE WHEN sp.stats_player_code = '1YDS' THEN spt.value ELSE 0 END)  AS 1yds,
				sum(CASE WHEN sp.stats_player_code = '1TD' THEN spt.value ELSE 0 END)  AS 1td,
				sum( CASE WHEN sp.stats_player_code = '1INT' THEN spt.value ELSE 0 END ) AS 1int,
				sum( CASE WHEN sp.stats_player_code = '1SK' THEN spt.value ELSE 0 END ) AS 1sk,
				sum( CASE WHEN sp.stats_player_code = '2REC' THEN spt.value ELSE 0 END ) AS 2rec,
				sum( CASE WHEN sp.stats_player_code = '2YDS' THEN spt.value ELSE 0 END ) AS 2yds,
				sum( CASE WHEN sp.stats_player_code = '2TD' THEN spt.value ELSE 0 END ) AS 2td,
				sum( CASE WHEN sp.stats_player_code = '3TKL' THEN spt.value ELSE 0 END ) AS 3tkl,
				sum( CASE WHEN sp.stats_player_code = '3INT' THEN spt.value ELSE 0 END ) AS 3int,
				sum( CASE WHEN sp.stats_player_code = '3SACK' THEN spt.value ELSE 0 END ) AS 3sk,
				sum( CASE WHEN sp.stats_player_code = '3PD' THEN spt.value ELSE 0 END ) AS 3pd,
				sum( CASE WHEN sp.stats_player_code = '3TD' THEN spt.value ELSE 0 END ) AS 3td,
				sum( CASE WHEN sp.stats_player_code = '4ATT' THEN spt.value ELSE 0 END ) AS 4att,
				sum( CASE WHEN sp.stats_player_code = '4YDS' THEN spt.value ELSE 0 END ) AS 4yds,
				sum( CASE WHEN sp.stats_player_code = '4TD' THEN spt.value ELSE 0 END ) AS 4td,
				sum( CASE WHEN sp.stats_player_code = '5UR' THEN spt.value ELSE 0 END ) AS 5ur,
				sum( CASE WHEN sp.stats_player_code = '5OC' THEN spt.value ELSE 0 END ) AS 5oc
				FROM member AS me
				LEFT JOIN stats_player_totals AS spt ON (me.member_id = spt.member_id)
				LEFT JOIN stats_player AS sp ON ( spt.stats_player_id = sp.stats_player_id)
				LEFT JOIN team_player AS tp ON ( tp.member_id = spt.member_id AND tp.team_id = spt.team_id AND spt.division_id = tp.division_id AND spt.league_id = tp.league_id AND spt.season_id = tp.season_id AND tp.position_id = spt.position_id )
				LEFT JOIN team AS t ON (spt.team_id = t.team_id)

				LEFT JOIN season AS s ON (s.season_id = tp.season_id)
				WHERE spt.league_id = '1' AND tp.league_id = '1' AND  spt.season_id = 'F18' AND tp.season_id = 'F18'  AND  `spt`.`competition_id` = '1' 
				GROUP BY tp.league_id, tp.division_id, tp.team_id, tp.member_id
			)
		
		as test_table
		 GROUP BY  season_id, league_id, member_id HAVING disciplinary_total > 0  ORDER BY disciplinary_total DESC