sqlmysql

Grouped results with counts using two fields


I have a local sports league database (this is relevant in order for me to explain the reason for asking). In this I have a team_matches table, and I've got in my application various ways to view the matches (by venue, by division, by date, etc.) This works pretty well, and one most of these methods, I'm able to show an options page which has the option and the number of matches - take venue, for example:

SELECT COUNT( me.scheduled_date ) AS 'number_of_matches', venue.id, venue.url_key, venue.name
FROM team_matches me
JOIN seasons season ON season.id = me.season
JOIN venues venue ON venue.id = me.venue
WHERE ( season.id = 9 )
GROUP BY venue.id
ORDER BY venue.name ASC

If I show these results (venue name and number of matches), I get something like the below: Venues with number of matches displayed

The problem is that one of these options is to view by team - I've not yet found a way to show this because the team could be either in the home_team or the away_team column, and I'm not sure how to show a list of teams that could be in either column, along with the counts, because the grouping surely has to be by whichever column the team appears in - if that makes sense? Grateful in advance for any assistance!


Solution

  • As the team can either be home_team or away_team, you need to consider both to count the number of matches per team.

    SELECT 
        team.id, 
        team.name, 
        COUNT(*) AS number_of_matches
    FROM (
        SELECT home_team AS team_id, scheduled_date
        FROM team_matches
        WHERE season = 9
        UNION ALL
        SELECT away_team AS team_id, scheduled_date
        FROM team_matches
        WHERE season = 9
    ) AS matches
    JOIN teams AS team ON team.id = matches.team_id
    GROUP BY team.id
    ORDER BY team.name ASC;