Delete Group of Records based on Group Statistic-CodePudding

I have the following two example tables

Teams

Team ID	Team Name
1	Bears
2	Tigers
3	Lions
4	Sharks

Players

Player ID	Name	Team ID	Playtime
1	John	1	5
2	Adam	1	4
3	Smith	1	5
4	Michelle	2	5
5	Stephanie	2	10
6	David	2	10
7	Courtney	2	2
8	Frank	2	7
9	Teresa	2	1
10	Michael	3	3
11	May	4	1
12	Daniel	4	1
13	Lisa	4	4

I need a select statement with the following criteria

Select all teams with less than 4 players
Figure out the total playtime for all players on those teams and sort the resulting table based on this in descending order

Based on the example given, I would want a table that looks like this:

Teams

Team Name	Num Players	Total Playtime
Bears	3	14
Sharks	3	6
Lions	1	3

Finally, I want to cut all even rows from the previous table, so the final table would look like:

Team Name	Num Players	Total Playtime
Bears	3	14
Lions	1	3

CodePudding user response：

You may try the following:

Query #1

SELECT
    t."Team Name",
    COUNT(p."Player ID") as "Num Players",
    SUM(p."Playtime") as "Total Playtime"
FROM
    teams t
LEFT JOIN
    players p ON t."Team ID"=p."Team ID"
GROUP BY
    t."Team Name"
HAVING
    COUNT(p."Player ID") < 4
ORDER BY 
    SUM(p."Playtime") DESC;

Team Name	Num Players	Total Playtime
Bears	3	14
Sharks	3	6
Lions	1	3

Query #2

SELECT
    t1."Team Name",
    t1."Num Players",
    t1."Total Playtime"
FROM (
SELECT
    ROW_NUMBER() OVER (ORDER BY SUM(p."Playtime") DESC) as rn,
    t."Team Name",
    COUNT(p."Player ID") as "Num Players",
    SUM(p."Playtime") as "Total Playtime"
FROM
    teams t
LEFT JOIN
    players p ON t."Team ID"=p."Team ID"
GROUP BY
    t."Team Name"
HAVING
    COUNT(p."Player ID") < 4
) t1
WHERE MOD(rn,2)=1
ORDER BY rn;

Team Name	Num Players	Total Playtime
Bears	3	14
Lions	1	3

View on DB Fiddle

Let me know if this works for you.