got a simple problem I'm trying to solve without success.
Given the following table
CREATE TABLE `scores` (
`proj` char(3) NOT NULL default '',
`id` int(11) NOT NULL default '0',
`score` double default NULL,
`cpid` char(32) default NULL,
`team` char(20) default NULL,
PRIMARY KEY (`proj`,`id`),
KEY `cpid` (`cpid`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
insert into scores values ('a',1,100,'aaa','X');
insert into scores values ('b',2,50,'aaa','X');
insert into scores values ('c',2,500,'aaa','Y');
I have the following sql to get the sum of scores for the cpid (cross
select cpid,sum(score) from scores group by cpid;
This is simple enough and works fine. However I also wish to select the team
given this case, I'd like to get 'X' as there are two instances of 'X' and
only one of 'Y'
Is this possible in the same sql statement, something like an AVG for a
string, or a median perhaps.
Help build our city at http://free-dc.myminicity.com !