Re: Group by function and avg on char

[Peter Brawley]

Phil,

If in the 2nd query you want teams with the highest count per cpid found 
in the first query, I think you can map the 'Avoiding repeat 
aggregation' pattern 
(http://www.artfulsoftware.com/infotree/queries.php) to your problem

PB

-----

Phil wrote:
> 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
>
>
>
>
>
>
>
>   
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG. 
> Version: 7.5.519 / Virus Database: 269.22.1/1346 - Release Date: 3/27/2008 10:03 AM
>