CODEWITHSUNDEEP
mysqlcount
Paul Ille
Paul Ille

Reputation: 1

Count query not grouping properly

I'm having some issues figuring out what seems to be simple, but it's eluding me. Any help is much appreciated.

CREATE TABLE IF NOT EXISTS `match_history` (
  `id` int(11) NOT NULL auto_increment,
  `match_id` int(11) NOT NULL,
  `team_id` int(11) NOT NULL,
  `player_id` int(11) NOT NULL,
  `map` varchar(150) NOT NULL,
  `score` int(11) NOT NULL,
  `outcome` varchar(25) NOT NULL,
  `notes` longtext NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=271 ;

match_id links to matches.id
team_id links to matchteams.id
player_id links to persons.id

What I want to see is wins and losses per team, but I'm having issues since the match_history table above will have multiple rows per match and per team.

For instance:

INSERT INTO `match_history` (`id`, `match_id`, `team_id`, `player_id`, `map`, `score`, `outcome`, `notes`) VALUES
(221, 44, 2, 124, 'Village', 1570, 'Win', ''),
(220, 44, 2, 115, 'Village', 1600, 'Win', ''),
(219, 44, 2, 92, 'Village', 2740, 'Win', ''),
(218, 44, 4, 105, 'Village',1000, 'Loss', ''),
(217, 44, 4, 111, 'Village', 1220, 'Loss', ''),
(216, 44, 4, 130, 'Village', 1440, 'Loss', ''),
(215, 44, 4, 122, 'Village', 2160, 'Loss', ''),
(214, 44, 4, 130, 'Seatown', 1410, 'Loss', ''),
(213, 44, 4, 122, 'Seatown', 1600, 'Loss', ''),
(212, 44, 4, 111, 'Seatown', 1790, 'Loss', ''),
(211, 44, 4, 105, 'Seatown', 1790, 'Loss', ''),
(210, 44, 2, 113, 'Seatown', 1020, 'Win', ''),
(209, 44, 2, 124, 'Seatown', 1480, 'Win', ''),
(207, 44, 2, 115, 'Seatown', 2850, 'Win', ''),
(208, 44, 2, 92, 'Seatown', 2160, 'Win', ''),
(222, 44, 2, 113, 'Village', 900, 'Win', ''),
(223, 45, 1, 123, 'Hardhat', 2970, 'Win', ''),
(224, 45, 1, 26, 'Hardhat', 2930, 'Win', ''),
(225, 45, 1, 107, 'Hardhat', 1710, 'Win', ''),
(226, 45, 3, 101, 'Hardhat', 1530, 'Loss', ''),
(227, 45, 3, 100, 'Hardhat', 1420, 'Loss', ''),
(228, 45, 3, 125, 'Hardhat', 1010, 'Loss', ''),
(229, 45, 1, 107, 'Seatown', 2520, 'Win', ''),
(230, 45, 1, 123, 'Seatown', 2260, 'Win', ''),
(231, 45, 1, 26, 'Seatown', 1560, 'Win', ''),
(232, 45, 3, 101, 'Seatown', 1510, 25, 3, 42, 0.6, 0, 0, 0, 'Loss', ''),

This is the query I was using, but it's counting each row as 1.

select mh.team_id as team_id, COUNT(distinct(mh.match_id)) as matches, 
  count(mh.map) as maps, mh.outcome, SUM(IF(mh.outcome='Win',1,0)) as wins,   
  SUM(IF(mh.outcome='Loss',1,0)) as losses, m.id, mt.name as teamname
FROM match_history mh, matches m, ladders l, match_teams mt
WHERE mh.team_id = mt.id and mh.match_id = m.id and 
  m.ladder_id = l.id and l.type = 'internal'
GROUP by mh.team_id
ORDER by wins desc

Upvotes: 0

Views: 154

Answers (2)

Bhrugesh Patel
Bhrugesh Patel

Reputation: 1106

I am not sure since havent tried sql in a while but try this.. let me know if it gives any error

select team_id, count(*)  from match_history
where outcome = 'Win'
group by team_id

if it works then we can union it with losses

EDIT: Such a troublesome thing... You better make a good use of this.. i have spent days (well, atleast minutes :P ) getting to this i have tested it in SQL Server to tailor it to mysql if needed. Good Luck! :D

select aa.tid, aa.win, COALESCE(c.loss, 0) from 
(SELECT distinct a.team_id tid, COALESCE(b.win, 0) win
  FROM [match_history] a
  left join
  (select team_id, count(*) win
  from [match_history]
  where outcome = 'Win'
  group by team_id) b
  on a.team_id = b.team_id)aa

  left join
  (select team_id, count(*) loss
  from [match_history]
  where outcome = 'Loss'
  group by team_id) c
  on aa.tid = c.team_id

Result:

tid win Loss
1   6   0
2   7   0
3   0   3
4   0   8

Upvotes: 0

ruakh
ruakh

Reputation: 183582

MySQL, unlike standard SQL, allows you to include columns in the field-list (the SELECT clause) that aren't in the GROUP BY and aren't aggregate columns — see http://dev.mysql.com/doc/refman/5.6/en/group-by-hidden-columns.html — but you have to be careful, or you can get unpredictable results. In your case, you're GROUPing BY mh.team_id, so you can safely include columns that depend on the team-ID (for example, mt.name should be safe), but you cannot select mh.outcome and m.id, since these depend on the specific match. So, I'll remove those columns.

Then, we need to change the SUM(1-or-0) to COUNT(DISTINCT match-ID-or-NULL) so that we only count distinct match-IDs:

select mh.team_id as team_id,
       COUNT(distinct(mh.match_id)) as matches,
       count(mh.map) as maps,
       COUNT(DISTINCT IF(mh.outcome='Win',m.id,NULL)) as wins,
       COUNT(DISTINCT IF(mh.outcome='Loss',m.id,NULL)) as losses,
       mt.name as teamname
  FROM match_history mh,
       matches m,
       ladders l,
       match_teams mt
 WHERE mh.team_id = mt.id
   and mh.match_id = m.id
   and m.ladder_id = l.id
   and l.type = 'internal'
 GROUP by mh.team_id
 ORDER by wins desc

which should do what you want.

Upvotes: 1

Related Questions