How to find the champion with the highest number of selections in match in SQLlite-CodePudding

I'm trying to do this query : find the champion with the highest number of selections in match for each champion found display their name as well as the number of times they ha\ve been selected in match .

Here are the tables:

 CREATE TABLE champions
    (
        id_champion INT PRIMARY KEY NOT NULL,
        name VARCHAR(20),
        title VARCHAR(20),
        attack INT,
        defense INT,
        magic INT,
        difficulty INT
    );
    
    CREATE TABLE players
    (
        id_player INT PRIMARY KEY NOT NULL,
        name VARCHAR(20),
        country VARCHAR(20)
    );
    
    CREATE TABLE matchs
    (
        id_match INT,
        player INT,
        champion INT,
        number INT,
        gold INT,
        team INT,
        position VARCHAR(10),
        PRIMARY KEY (id_match, player),
        FOREIGN KEY(id_match) REFERENCES matchsmeta(id_match) ON DELETE SET NULL,
        FOREIGN KEY(player) REFERENCES players(id_player) ON DELETE SET NULL,
        FOREIGN KEY(champion) REFERENCES champions(id_champion) ON DELETE SET NULL
    
    );
    
    CREATE TABLE matchsmeta
    (
        id_match INT PRIMARY KEY NOT NULL,
        time INT,
        victory INT,
        date DATE,
        game_mode VARCHAR(10)
    );

CodePudding user response：

First aggregate in the table matchs to get the number of times each champion was picked and use MAX() window function to get the max of these counts:

SELECT champion, 
       COUNT(*) counter,
       MAX(COUNT(*)) OVER () max_counter
FROM matchs 
GROUP BY champion

Then join the above query to the table champions:

SELECT c.name, m.counter
FROM champions c 
INNER JOIN (
  SELECT champion, 
         COUNT(*) counter,
         MAX(COUNT(*)) OVER () max_counter
  FROM matchs 
  GROUP BY champion
) m ON m.champion = c.id_champion
WHERE m.counter = m.max_counter;

CodePudding user response：

You have to make the count of times a champion appears in matchs table. Then you can join the champions table to get the name.

SELECT
m.champion,
c.name,
SUM(1) as num_times_picked
FROM matchs m
LEFT JOIN champions c on (m.champion = c.id_champion)
GROUP BY m.champion
ORDER BY 3 DESC