We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
- Prepare
- SQL
- Advanced Join
- Interviews
- Discussions
Interviews
Interviews
Sort by
recency
|
1281 Discussions
|
Please Login in order to post a comment
run code is successfull but submit still loading?
The results are wrong and i dont know where the issue is, any help to put me on the right track will be appriciated: select c.contest_id,c.hacker_id,c.name,sum(s.total_submissions)as total_sub ,sum(s.total_accepted_submissions) as total_acc, sum(v.total_views)as total_v,sum(v.total_unique_views)as total_uv from contests c left join colleges co on c.contest_id = co.contest_id left join challenges ch on co.college_id = ch.college_id left join view_stats v on ch.challenge_id = v.challenge_id left join submission_stats s on ch.challenge_id = s.challenge_id group by c.contest_id,c.hacker_id,c.name having total_sub > 0 or total_acc > 0 or total_v > 0 or total_uv > 0 order by c.contest_id asc
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;
SELECT c.contest_id, c.hacker_id,c.name , s.ts, s.tas, v.tv, v.tuv from contests c inner join (SELECT c.contest_id, sum(total_submissions) as ts, sum(total_accepted_submissions) as tas FROM contests c INNER JOIN colleges cl ON cl.contest_id=c.contest_id INNER JOIN challenges ch ON cl.college_id=ch.college_id INNER JOIN submission_stats ss ON ss.challenge_id=ch.challenge_id GROUP BY c.contest_id having SUM(total_submissions)+SUM(total_accepted_submissions) > 0) s ON c.contest_id=s.contest_id INNER JOIN ( SELECT c.contest_id, sum(total_views) as tv, sum(total_unique_views) as tuv FROM contests c INNER JOIN colleges cl ON cl.contest_id=c.contest_id INNER JOIN challenges ch ON cl.college_id=ch.college_id INNER JOIN view_stats vs ON vs.challenge_id=ch.challenge_id GROUP BY c.contest_id having SUM(total_views)+SUM(total_unique_views) > 0 ) v ON v.contest_id=s.contest_id;
please can anyone help what is the error here: ERROR 1140 (42000) at line 4: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'run_ihswtf6m3vy.Submission_Stats.challenge_id'; this is incompatible with sql_mode=only_full_group_by
SELECT CT.contest_id, hacker_id,name, TS, TAS, TV, TUV FROM Contests AS CT JOIN Colleges AS CL ON CT.contest_id = CL.contest_id
JOIN Challenges AS CH ON CH.college_id = CL.college_id JOIN (SELECT challenge_id, SUM(total_views) AS TV, SUM(total_unique_views) AS TUV FROM View_Stats ) AS VS ON CH.challenge_id = VS.challenge_id JOIN (SELECT challenge_id, SUM(total_submissions) AS TS, SUM(total_accepted_submissions) AS TAS FROM Submission_Stats ) AS SS ON CH.challenge_id = SS.challenge_id GROUP BY CT.contest_id, hacker_id,name ORDER BY CT.contest_id