How to solve query in MySql-CodePudding

I have some database I need to query. It is necessary to write a query that finds out how many times the teams played among themselves. It is important if Team A plays against Team B and then Team B plays against Team A, it counts as the same event. That is, the result should be: A against B - 2 games.

SELECT
     least(home_team, away_team) AS A
    ,greatest(home_team, away_team) AS B
    ,COUNT(*) AS games_count
FROM event_entity
GROUP BY A, B
HAVING COUNT(*) >= 1                        
ORDER BY A, B

I have this query.

But need this solve. 3 first rows in solving

Novara-Udinese 1 
Brescia-Genoa 1 
Fc Pacos de Ferreira - Trofense 1

This is my schema:

enter image description here

CodePudding user response：

You already have the query that you need to build on the logic of getting the number of count of games. In order to display the event you can do as follows.

SELECT MAX(CONCAT(home_team,'-',away_team)) as game
     ,COUNT(*) AS games_count
 FROM event_entity
GROUP BY least(home_team, away_team), greatest(home_team, away_team)
ORDER BY 1