CODEWITHSUNDEEP
mysqljoincount
user1745447
user1745447

Reputation: 37

Using Count on a Join - MYSQL

I have two tables, one with a group of teams and info about them. The other table has info about games they have played. I wish to count how many games a specific team has played.

I've got team_number, team_name on one table named team. I've got played_team on the other table named played, which is a foreign key to team_number.

How can I code a join to count the number of occurrences of played_team and join it with team_number and team_name?

I am using MYSQL 5.1, I hope this makes sense. Thanks

Upvotes: 0

Views: 92

Answers (1)

tynor
tynor

Reputation: 16

How about:

select teams.`team_name`, count(matches.`played_team`) 
from teams 
join matches on teams.`team_number` = matches.`played_team` 
group by teams.`team_name`

Here are the tables I used:

CREATE TABLE `teams` (
  `team_number` int(11) NOT NULL AUTO_INCREMENT,
  `team_name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`team_number`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `teams` (`team_number`, `team_name`)
VALUES
    (1,'a'),
    (2,'b'),
    (3,'c'),
    (4,'d');

CREATE TABLE `matches` (
  `played_team` int(11) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `match_title` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `played_team` (`played_team`),
  CONSTRAINT `matches_ibfk_1` FOREIGN KEY (`played_team`) REFERENCES `teams` (`team_number`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `matches` (`played_team`, `id`, `match_title`)
VALUES
    (1,1,'match_a'),
    (1,2,'match_b'),
    (3,3,'match_c'),
    (4,4,'match_d');

And here are the results of the query:

team_name    count(matches.`played_team`)  
a            2  
c            1  
d            1

Upvotes: 1

Related Questions