0

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. enter image description here

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.

.enter image description here

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'
New contributor
kenny is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • MySQL 8.0 has RANK(), etc. – Rick James Sep 11 at 20:37
  • Unclear. Sample output does not match to sample source data. For example, there is 3 records for Cat 3+Futbol for ROJO (id = 3,8,9) whereas only 2 games in output (id = 3 is skipped). Why? – Akina Sep 12 at 4:31
1

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

  • If it is very true, if the team loses 2 matches in this case turquoise is -6, but for the user it should be total = 0 – kenny Sep 12 at 14:13
  • modify in this part to avoid negatives and calculate the total well: }SUM (3 * SIGN (score1-score3> = 0)) TOTAL Thank you all for your time. – kenny Sep 12 at 16:35
  • @kenny SIGN(score1-score3>=0) == (score1>=score3) – Akina Sep 12 at 19:02

Your Answer

kenny is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.