List:General Discussion« Previous MessageNext Message »
From:Phil Date:March 27 2008 4:51pm
Subject:Group by function and avg on char
View as plain text  
Hi all,

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
project id)

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.

Regards

Phil







-- 
Help build our city at http://free-dc.myminicity.com !

Thread
Group by function and avg on charPhil27 Mar
  • Re: Group by function and avg on charPeter Brawley29 Mar