How do I get TABLE OF TOURNAMENT POSITIONS according to score table 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'
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&gt;score3) PG, 
       SUM(score1&lt;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&gt;score3) PG, 
       SUM(score1&lt;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