How do I get a list with top 5 and then all the others as the 6'th item in SQL?-CodePudding

So I have a table that stores a line each time a team scores a goal:

| id | time |  team  | level |
------------------------------
|  1 | #### | Team 1 |   A   |
|  2 | #### | Team 2 |   B   |
...

I'm trying to get top 5 teams on level A with most goals and then combine all the rest as the 6th item in the list, like this:

|   team  |  goals |
-------------------
|  Team 1 |   13   |
|  Team 5 |   12   |
| Team 34 |    9   |
|  Team 7 |    7   |
| Team 19 |    7   |
|  Other  |   54   |

How do I get this? I've tried something like this:

SELECT team, goals
FROM (
    SELECT team, COALESCE(SUM(CASE WHEN level = 'A' THEN 1 ELSE 0 END), 0) AS goals 
    FROM goals
    WHERE level = A
    GROUP BY team
    ORDER BY goals DESC
    LIMIT 5
) AS g

UNION ALL

So this gives me the top 5, but I don't know how to make the second part which is the "other", because it has to exclude the teams from the top 5...

CodePudding user response：

Study also the case of equality sum of goals in TOP-5 teams. See functions ROW_NUMBER(), RANK() and DENSE_RANK()

SELECT team
     , SUM(goal_total) goal_total
  FROM
     (
SELECT CASE
       WHEN ROW_NUMBER()
            OVER (ORDER BY COUNT(*) desc) <=5
       THEN team
       ELSE 'Other'
       END
       AS team
     , CASE
       WHEN ROW_NUMBER()
            OVER (ORDER BY COUNT(*) desc) <=5
       THEN ROW_NUMBER()
            OVER (ORDER BY COUNT(*) desc)
       ELSE 6
       END
       AS place
     , COUNT(*)
       AS goal_total
  FROM goals
 WHERE level = 'A'
 GROUP 
    BY team
     )
 GROUP
    BY team
     , place
 ORDER
    BY place

CodePudding user response：

Use ROW_NUMBER to rank your teams. Use CASE WHEN for the groups:

SELECT CASE WHEN rn <= 5 THEN team ELSE 'others' END AS team, sum(goals) as goals
FROM (
    SELECT 
      team, 
      COUNT(*) AS goals,
      ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rn
    FROM goals
    WHERE level = 'A'
    GROUP BY team
) AS g
GROUP BY CASE WHEN rn <= 5 THEN team ELSE 'others' END
ORDER BY MAX(rn);

CodePudding user response：

Try this,

create table #temp(team varchar(50),goal int)
insert into #temp 
SELECT team, COALESCE(SUM(CASE WHEN level = 'A' THEN 1 ELSE 0 END), 0) AS goals 
    FROM goals
    WHERE level = A
    GROUP BY team
    ORDER BY goals DESC
    LIMIT 5

    select team,goal from #temp

    union all

    SELECT team, COALESCE(SUM(CASE WHEN level = 'A' THEN 1 ELSE 0 END), 0) AS goals 
    FROM goals g
    WHERE level = A
    and not exists (select 1 from #temp t where g.team=t.team)
    GROUP BY team
    --ORDER BY goals DESC

    drop table #temp

CodePudding user response：

Combining the aggregate and the window function it could be a bit shorter

SELECT case rn when 6 then 'others' else team end gteam, sum(goals) goals
FROM (
    SELECT team, count(*) goals, 
         least(6, row_number() over(order by count(*))) rn
    FROM goals
    WHERE level = A
    GROUP BY team
) t
GROUP BY gteam, rn
ORDER BY rn