Untitled

SELECT c.contest_id, c.hacker_id, c.name,
SUM(tsubs), SUM(taccepted_subs), SUM(tviews), SUM(tunique_views)
FROM contests c, colleges o, challenges h left join
(SELECT challenge_id, SUM(total_views) tviews, SUM(total_unique_views) tunique_views FROM view_stats
GROUP BY challenge_id) v
ON h.challenge_id = v.challenge_id left join
(SELECT challenge_id, SUM(total_submissions) tsubs, SUM(total_accepted_submissions) taccepted_subs FROM submission_stats
GROUP BY challenge_id) s
ON h.challenge_id = s.challenge_id
WHERE c.contest_id = o.contest_id
AND o.college_id = h.college_id
GROUP BY c.contest_id, c.hacker_id, c.name
HAVING (SUM(tsubs) + SUM(taccepted_subs) + SUM(tviews) + SUM(tunique_views)) > 0
ORDER BY c.contest_id;