SELECT name, SUM(runs_scored) as runs FROM(SELECT *, SUM(output) OVER(PARTITION BY name) as total_no FROM(SELECT *,CASE WHEN runs_scored < 50 THEN 0 ELSE 1 END as output FROM (SELECT m.match_id, p.name, m.runs_scored FROM `matches` m JOIN players p ON m.player_id = p.id WHERE p.id NOT IN(SELECT id FROM players WHERE id IN(SELECT player_id FROM matches WHERE runs_scored = 0))) as t) as x) as y WHERE total_no >1 GROUP BY name;
your input of id is not correct . matches table it has 310 and players table id has 301 with cte as( select p.name,count(*) as cnt from matches m join players p on p.id=m.player_id where m.runs_scored between 50 and 100 group by 1 ), cte1 as ( select p.name,m.runs_scored from matches m join players p on p.id =m.player_id where runs_scored=0 ), cte2 as( select p.name,sum(m.runs_scored) as total_run from matches m join players p on p.id =m.player_id group by 1 ) select c.name,c2.total_run from cte c join cte2 c2 on c.name=c2.name join cte1 c1 on c.namec1.name where c.cnt>1
Simple Solution with out using CTE SELECT name, sum(runs_scored) AS total_runs FROM Matches JOIN Players ON Matches.player_id = Players.id GROUP BY player_id HAVING ( COUNT(CASE WHEN runs_scored >= 50 THEN 1 END) >=2 AND COUNT(CASE WHEN runs_scored = 0 THEN 1 END) = 0 ) ORDER BY total_runs DESC
with cte as ( select player_id,runs_scored from matches1 where runs_scored =0) , cte1 as ( select player_id,runs_scored from matches1 where runs_scored >= 50 ), cte3 as ( select c1.*, count(c1.player_id) over( partition by c1.player_id ) as cnt from cte c right join cte1 c1 on c.player_id = c1.player_id where c.runs_scored is null ) select p.name,sum(c3.runs_scored) as total_score from cte3 c3 inner join players1 p on c3.player_id = p.id where cnt = 2 group by p.name
WITH CTE AS( SELECT *,SUM(CASE WHEN runs_scored = 0 THEN 0 ELSE 1 END ) OVER (PARTITION BY player_id ORDER BY player_id)DECCOUNT ,COUNT(player_id ) OVER (PARTITION BY player_id ORDER BY player_id)TOTALCOUNT FROM matches) , A AS( SELECT match_id, player_id, runs_scored, SUM(CASE WHEN runs_scored>=50 THEN 1 ELSE 0 END ) OVER (PARTITION BY player_id ORDER BY player_id)TOTAL FROM CTE WHERE DECCOUNT=TOTALCOUNT) SELECT P.name,SUM(runs_scored)TOTALRUNS FROM A JOIN players P ON P.id=A.player_id WHERE TOTAL =2 GROUP BY P.name
