actually, you can, but they all have to be within an aggregate function
if you want counts per team (as your last example suggested), you also need a GROUP BY clause
SELECT tblCPReached.Team AS 'Team Name'
, COUNT(*) AS 'CPs Collected'
ON tblRegistrationBasic.EventID = tblEvents.EID
ON tblCategory.CategoryID = tblRegistrationBasic.CategoryID
ON tblCPReached.Team = tblRegistrationBasic.RID
WHERE tblEvents.EID = Request.QueryString("id")
BY COUNT(*) DESC