MySQL query with 2 COUNT() of other tables with where conditions

Question

I've a table called sports that contains a list of list of sports, other called seasons that contains the seasons for a specific sport and competitions that have the competitions of a specific sport and season.

I need one MySQL query to print the list of sports with how much seasons and competitions has each. My tables structure:

sports

+--------------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| id                 | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name               | varchar(32)      | NO   |     | NULL    |                |
| slug               | varchar(45)      | NO   |     | NULL    |                |
| description        | varchar(128)     | NO   |     | NULL    |                |
+--------------------+------------------+------+-----+---------+----------------+

seasons

+--------------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| id                 | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| id_sport           | int(10) unsigned | NO   | MUL | NULL    |                |
| name               | varchar(32)      | NO   |     | NULL    |                |
| slug               | varchar(32)      | NO   |     | NULL    |                |
+--------------------+------------------+------+-----+---------+----------------+

competitions

+--------------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| id                 | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| id_season          | int(10) unsigned | NO   | MUL | NULL    |                |
| name               | varchar(32)      | NO   |     | NULL    |                |
| slug               | varchar(64)      | NO   |     | NULL    |                |
| description        | varchar(128)     | YES  |     | NULL    |                |
+--------------------+------------------+------+-----+---------+----------------+

The result of my query needs to contain: sports.*, total_seasons (SUM of seasons where seasons.id_sport=sports.id) and total_competitions (SUM of competitions where competitions.id_season=seasons.id AND seasons.id_sport=sports.id).

Thank you in advance!

Please confirm something... The seasons can have multiple sports simultaneously overlapping... correct? — DRapp, Dec 31 '10 at 15:14
@DRapp: Each sport creates seasons. They are not shared between sports. So football will have season 2010-2011 with id=1 and id_sport=1 and soccer season 2010-2011 with id=2 and id_sport=2, for example. Thank you! — ipalaus, Dec 31 '10 at 15:22

score 1 · Answer 1 · answered Dec 31 '10 at 15:31

1

SELECT sports.*,
    COUNT(seasons.id) AS total_seasons,
    COUNT(competitions.id) AS total_competitions
    FROM sports, seasons, competitions
    WHERE sports.id=id_sport AND seasons.id=id_season
    GROUP BY sports.id

answered Dec 31 '10 at 15:31

Borealid

95,191
9
106
122

Thank you Borealid but the same I've commented to DRapp. If some sport don't have competition or season I don get the result. How can I solve it? Thank you! – ipalaus Dec 31 '10 at 16:27

DRapp · Answer 2 · 2010-12-31T17:41:42.167

1

select
      sports.id,
      sports.name,
      sports.slug,
      sports.description,
      count( distinct seasons.id ) as DistinctSeasons,
      count( * ) as TotalCompetitions
   from 
      sports 
         left outer join Seasons
            on sports.id = seasons.id_sport,
         left outer join Competitions
            on seasons.id = Competitions.id_season
   group by
      sports.id

edited Dec 31 '10 at 17:41

answered Dec 31 '10 at 15:32

DRapp

47,638
12
72
142

Thank you DRapp, but I don't understand why if some of the sports don't have a season or competition I don't have result for that. How can I solve it? Thank you! – ipalaus Dec 31 '10 at 16:25
@Isern Palaus, I changed my version to left outer join as suggested by @ajreal, but his version also includes check if no such matching to wrap the counting within IFNULL()... So if there are no such matchings, you will get a zero value instead of NULL in the result set. – DRapp Dec 31 '10 at 17:43

ajreal · Accepted Answer · 2011-01-01T12:37:07.717

1

use left join instead of inner join

select
  sports.id,
  sports.name,
  sports.slug,
  sports.description,
  ifnull(count( distinct seasons.id ), 0) as DistinctSeasons,
  ifnull(count( distinct competitions.id ), 0) as TotalCompetitions
from 
  sports
  left join seasons on sports.id=seasons.id_sport
  left join competitions on seasons.id = competitions.id_season
group by
  sports.id;

edited Jan 01 '11 at 12:37

answered Dec 31 '10 at 16:50

ajreal

46,720
11
89
119

It's all fine but I get TotalCompetitions as 1 when there is no season for a competition. If there is no season can't be a competition. How can I fix it? Thank you in advance, it works fine but only this detail! Happy new year! – ipalaus Dec 31 '10 at 18:44
@Isern Palaus - the problem is caused by the use of asterisk, the update version should help (or does not ...) – ajreal Jan 01 '11 at 12:37

MySQL query with 2 COUNT() of other tables with where conditions

3 Answers3

Linked