Can someone correct this - WITH total as ( select challenge_id, SUM(v.total_views),SUM(v.total_unique_views), SUM(s.total_submissions), SUM(s.total_accepted_submissions) from View_Stats v JOIN Submission_Stats s group by challenge_id ) select cs.,c.,cl.,t. from Contests cs join Colleges c on cs.contest_id = c.contest_id join Challenges cl on c.challenge_id = cl.challenge_id join total t on cl.challenge_id = t.challenge_id

what is wrong with this query

select c.contest_id, c.hacker_id, c.name, sum(vs.total_views), sum(vs.total_unique_views), sum(ss.total_submissions), sum(ss.total_accepted_submissions) from contests c left join Colleges Col on c.contest_id = col.contest_id left join Challenges ch on col.college_id = ch.college_id left join View_Stats VS on ch.challenge_id = vs.challenge_id left join Submission_Stats SS on ch.challenge_id = ss.challenge_id group by c.contest_id, c.hacker_id, c.name having sum(vs.total_views) + sum(vs.total_unique_views) + sum(ss.total_submissions) + sum(ss.total_accepted_submissions) > 0 order by c.contest_id

MICROSOFT SQL SERVER

with s_b as (select c.contest_id, sum(isnull (s.total_submissions, 0)) as to_sub, sum(isnull (s.total_accepted_submissions, 0)) as to_ac_sub from Contests c join Colleges cg on c.contest_id = cg.contest_id join Challenges cl on cg.college_id = cl.college_id left join Submission_Stats s on cl.challenge_id = s.challenge_id group by c.contest_id),

_view as (select c.contest_id, sum(isnull (v.total_views, 0)) as to_view, sum(isnull (v.total_unique_views, 0)) as to_uq_view from Contests c join Colleges cg on c.contest_id = cg.contest_id join Challenges cl on cg.college_id = cl.college_id left join View_Stats v on cl.challenge_id = v.challenge_id group by c.contest_id)

select c.contest_id, c.hacker_id, c.name, s.to_sub, s.to_ac_sub, v.to_view, v.to_uq_view from Contests c join s_b s on c.contest_id = s.contest_id join _view v on c.contest_id = v.contest_id order by c.contest_id;

SELECT c1.contest_id, c1.hacker_id, c1.name, SUM(s.total_submissions) AS TotalSubmission, SUM(s.total_accepted_submissions) AS TotalAcceptedSubmission, SUM(v.total_views) AS TotalViews, SUM(v.total_unique_views) AS TotalUniqueViews FROM Contests c1 JOIN Colleges c2 ON c1.contest_id = c2. contest_id JOIN Challenges c3 ON c2.college_id = c3.college_id LEFT JOIN ( SELECT c3.challenge_id, SUM(COALESCE(ss.total_submissions, 0)) AS total_submissions, SUM(COALESCE(ss.total_accepted_submissions, 0)) AS total_accepted_submissions FROM Challenges c3 LEFT JOIN Submission_Stats ss ON ss.challenge_id = c3.challenge_id GROUP BY c3.challenge_id ) AS s ON c3.challenge_id = s.challenge_id LEFT JOIN ( SELECT c3.challenge_id, SUM(COALESCE(vs.total_views, 0)) AS total_views, SUM(COALESCE(vs.total_unique_views, 0)) AS total_unique_views FROM Challenges c3 LEFT JOIN View_Stats vs ON vs.challenge_id = c3.challenge_id GROUP BY c3.challenge_id ) AS v ON c3.challenge_id = v.challenge_id GROUP BY c1.contest_id, c1.hacker_id, c1.name HAVING SUM(s.total_submissions + s.total_accepted_submissions + v.total_views + v.total_unique_views) > 0 ORDER BY c1.contest_id;

SELECT con.contest_id, con.hacker_id, con.name, SUM(ss.total_submissions) AS Total_submissions, SUM(ss.total_accepted_submissions) AS Total_accepted_submissions, Sum(vie.total_views) AS Total_views, SUM(vie.total_unique_views) AS Total_unique_views FROM Contests AS con INNER JOIN Colleges AS col ON con.contest_id = col.contest_id INNER JOIN Challenges AS cha ON col.college_id = cha.college_id LEFT JOIN (SELECT ch.challenge_id, SUM(total_views) AS total_views, SUM(total_unique_views) AS total_unique_views FROM Challenges AS ch LEFT JOIN View_Stats AS vs USING(challenge_id) GROUP BY ch.challenge_id) AS vie ON cha.challenge_id = vie.challenge_id LEFT JOIN (SELECT ch.challenge_id, SUM(sus.total_submissions) AS total_submissions, SUM(sus.total_accepted_submissions) AS total_accepted_submissions FROM Challenges AS ch LEFT JOIN Submission_Stats AS sus USING(challenge_id) GROUP BY ch.challenge_id) AS ss ON cha.challenge_id = ss.challenge_id GROUP BY con.contest_id, con.hacker_id, con.name HAVING SUM(ss.total_submissions) <> 0 AND SUM(ss.total_accepted_submissions) <> 0 AND Sum(vie.total_views) <> 0 AND SUM(vie.total_unique_views) <> 0 ORDER BY con.contest_id