List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:March 29 2008 6:37am
Subject:Re: Group by function and avg on char
View as plain text  
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
>   

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