Untitled

SELECT contest_id, hacker_id, name, A, B, C, D FROM
(SELECT contests.contest_id, contests.hacker_id, contests.name,

   (SELECT SUM(s.total_submissions) FROM submission_stats s 
    JOIN challenges c ON s.challenge_id = c.challenge_id
    JOIN colleges g ON c.college_id = g.college_id
    WHERE g.contest_id = contests.contest_id) as A,

   (SELECT SUM(s.total_accepted_submissions) FROM submission_stats s 
    JOIN challenges c ON s.challenge_id = c.challenge_id
    JOIN colleges g ON c.college_id = g.college_id
    WHERE g.contest_id = contests.contest_id) as B,

   (SELECT SUM(v.total_views) FROM view_stats v
    JOIN challenges c ON v.challenge_id = c.challenge_id
    JOIN colleges g ON c.college_id = g.college_id
    WHERE g.contest_id = contests.contest_id) as C,

   (SELECT SUM(v.total_unique_views) FROM view_stats v
    JOIN challenges c ON v.challenge_id = c.challenge_id
    JOIN colleges g ON c.college_id = g.college_id
    WHERE g.contest_id = contests.contest_id) as D

FROM contests

GROUP BY contests.contest_id, contests.hacker_id, contests.name) as X
WHERE A IS NOT NULL OR B IS NOT NULL OR C IS NOT NULL OR D IS NOT NULL
ORDER BY contest_id;