mysql – How to Get Table of Tournament Positions According to Score Table

MySQL

Buen dia Comunidad.

Good morning Community.

I am having doubts about how to obtain the POSITION CHART of the following SCORE table in MYSQL.

1: This is my table where the user records the escort of each sport played.

2: Now this is where I have doubts, how do I get the POSITION CHART according to the attached model where the following is calculated: PJ = Matches played PG = Matches Won PP = Lost Matches PE = Draws Matched GF = Goals For GC = Goals Against DFG = Total Goal Difference = where the winning team obtains 3 Points.

I wait for your kind support

I could only make the amount of encounter that 1 team has made

select category, report, gender, Team1, count (*) from score where Team1 = 'red' and category = 'CAT 3'

Best Answer

Looking the task and sample data and ignoring sample output...

For MySQL 8+ :

WITH cte AS (
SELECT categoria,ndeporte,equipo1,score1,equipo3,score3
FROM score
UNION ALL
SELECT categoria,ndeporte,equipo3,score3,equipo1,score1
FROM score
)
SELECT UPPER(equipo1) EQUIPOS, 
       COUNT(*) PJ, 
       SUM(score1>score3) PG, 
       SUM(score1<score3) PP, 
       SUM(score1=score3) PE, 
       SUM(score1) GF,
       SUM(score3) GC,
       SUM(score1-score3) DfG,
       SUM(3*SIGN(score1-score3)) TOTAL
FROM cte 
WHERE categoria = 'CAT 3'
  AND ndeporte = 'Futbol'
  GROUP BY UPPER(equipo1);

... and for MySQL 5+ :

SELECT UPPER(equipo1) EQUIPOS, 
       COUNT(*) PJ, 
       SUM(score1>score3) PG, 
       SUM(score1<score3) PP, 
       SUM(score1=score3) PE, 
       SUM(score1) GF,
       SUM(score3) GC,
       SUM(score1-score3) DfG,
       SUM(3*SIGN(score1-score3)) TOTAL
FROM ( SELECT categoria,ndeporte,equipo1,score1,equipo3,score3
       FROM score
       UNION ALL
       SELECT categoria,ndeporte,equipo3,score3,equipo1,score1
       FROM score) cte 
WHERE categoria = 'CAT 3'
  AND ndeporte = 'Futbol'
  GROUP BY UPPER(equipo1);

If you need the data for all categoria and/or ndeporte then add them to output fields list and GROUP BY clause, and remove from WHERE clause (or edit if you need the result for some values only).

fiddle

Related Solutions

MySQL – How to Get the Rank of a User in a Score Table

SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores

gives this list:

id name  score rank
1  Ida   100   2
2  Boo    58   5
3  Lala   88   4
4  Bash  102   1
5  Assem  99   3

Getting a single person score:

SELECT id, name, score, FIND_IN_SET( score, (    
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores
WHERE name =  'Assem'

Gives this result:

id name score rank
5 Assem 99 3

You'll have one scan to get the score list, and another scan or seek to do something useful with it. An index on the score column would help performance on large tables.

Mysql – How to design tables for players in a tournament

UPDATED version based on feedback:

CREATE TABLE group (
    group_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255),
    PRIMARY KEY(team_id)
);

CREATE TABLE player (
    player_id INT NOT NULL AUTO_INCREMENT,
    last_name VARCHAR(50),
    first_name VARCHAR(50),
    group_id INT,
    player_rank INT,
    PRIMARY KEY(player_id)
);

CREATE TABLE game (
    game_id INT NOT NULL AUTO_INCREMENT,
    match_date DATE,
    overtime TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (game_id)
);

CREATE TABLE player_game (
    game_id INT NOT NULL,
    player_id INT NOT NULL,
    player_score INT,
    PRIMARY KEY (game_id, player_id)
);

...and UPDATED explanation: I think group and player are self-explanatory. The table game will have one record per match. The player_game table records the players in a match and the results; each match will have two records, one for each player.

Since you appear to be using InnoDB as the storage engine you may want to use foreign keys to enforce referential integrity, and obviously you can add other constraints and indexes as needed. If you need some functionality that this doesn't seem to cover, ask and I will either explain or add it.

Best Answer

Related Solutions

MySQL – How to Get the Rank of a User in a Score Table

Mysql – How to design tables for players in a tournament

Related Question