Untitled

SELECT  con.contest_id,
        con.hacker_id, 
        con.name, 
        SUM(total_submissions), 
        SUM(total_accepted_submissions), 
        SUM(total_views), 
        SUM(total_unique_views)
FROM contests con 
JOIN colleges col ON con.contest_id = col.contest_id 
JOIN challenges cha ON  col.college_id = cha.college_id 
LEFT JOIN
    (SELECT 
        challenge_id, 
        SUM(total_views) AS total_views, 
        SUM(total_unique_views) AS total_unique_views
        FROM view_stats GROUP BY challenge_id) vs 
    ON cha.challenge_id = vs.challenge_id 
LEFT JOIN
    (SELECT 
        challenge_id, 
        SUM(total_submissions) AS total_submissions, 
        SUM(total_accepted_submissions) AS total_accepted_submissions 
        FROM submission_stats GROUP BY challenge_id) ss 
    ON cha.challenge_id = ss.challenge_id
GROUP BY con.contest_id, con.hacker_id, con.name
HAVING  SUM(total_submissions)!=0 OR 
        SUM(total_accepted_submissions)!=0 OR
        SUM(total_views)!=0 OR
        SUM(total_unique_views)!=0
ORDER BY contest_id;