SELECT
	MAX(t.majorCatId) AS tCat, 
	MAX(t.name) AS tName,
	COUNT(t.name) AS tTimes, 
	AVG(ps.round1) as avgRd1,
	AVG(ps.round2) as avgRd2,
	AVG(ps.round3) as avgRd3,
	AVG(ps.round4) as avgRd4,
	AVG(ps.rank) as avgRank,
	MAX(ps.rank) AS lowRank,
	MIN(ps.rank) AS highRank,
	MAX(ps.round1+ps.round2+ps.round3+ps.round4) AS highScore,
	MIN(ps.round1+ps.round2+ps.round3+ps.round4) AS lowScore,
	AVG(ps.round1+ps.round2+ps.round3+ps.round4) AS avgScore
FROM playerscores ps
JOIN tournaments t ON t.tournamentId=ps.tournamentId
WHERE ps.playerId = 2
GROUP BY t.majorCatId
ORDER BY MAX(t.startDate)

SELECT
	MAX(t.majorCatId) AS tCat, 
	MAX(t.name) AS tName,
	COUNT(t.name) AS tTimes, 
	AVG(NULLIF(ps.round1,0)) as avgRd1,
	AVG(NULLIF(ps.round2,0)) as avgRd2,
	AVG(NULLIF(ps.round3,0)) as avgRd3,
	AVG(NULLIF(ps.round4,0)) as avgRd4,
	AVG(NULLIF(ps.rank,0)) as avgRank,
	MAX(NULLIF(ps.rank,0)) AS lowRank,
	MIN(NULLIF(ps.rank,0)) AS highRank,
	MAX(CASE	
			WHEN ps.round1>0 AND ps.round2>0 AND ps.round3>0 AND ps.round4>0 THEN (ps.round1+ps.round2+ps.round3+ps.round4) 
		END		
	) AS highScore,
	MIN(CASE	
			WHEN ps.round1>0 AND ps.round2>0 AND ps.round3>0 AND ps.round4>0 THEN (ps.round1+ps.round2+ps.round3+ps.round4) 
		END
	) AS lowScore,
	AVG(
		CASE	
			WHEN ps.round1>0 AND ps.round2>0 AND ps.round3>0 AND ps.round4>0 THEN (ps.round1+ps.round2+ps.round3+ps.round4)
		END
	) AS avgScore
FROM playerscores ps
JOIN tournaments t ON t.tournamentId=ps.tournamentId
WHERE ps.playerId = 2
GROUP BY t.majorCatId
ORDER BY MAX(t.startDate)