From: Peter Brawley
Date: January 7 2009 4:30pm
Subject: Re: Group by question
List-Archive: http://lists.mysql.com/mysql/215847
Message-Id: <4964D892.9090006@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------040808040405000808020509"

--------------040808040405000808020509
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Phil

 >is there any way to modify this query so that it would
 >return the team having the most entries?

See "Within-group aggregates" at http://www.artfulsoftware.com/queries.php

PB

-----

Phil wrote:
> A question on grouping I've never been able to solve...
>
> create table j (proj char(3), id int, score double,cpid char(32),team
> char(10));
>
> insert into j values('aaa',1,100,'aaaaaaaaaaaaaaaaa','team1');
> insert into j values('bbb',2,200,'aaaaaaaaaaaaaaaaa','team1');
> insert into j values('ccc',3,300,'aaaaaaaaaaaaaaaaa','team2');
> insert into j values('aaa',4,100,'bbbbbbbbbbbbbbbbb','team2');
> insert into j values('bbb',5,300,'bbbbbbbbbbbbbbbbb','team1');
> insert into j values('ccc',6,400,'bbbbbbbbbbbbbbbbb','team1');
> insert into j values('aaa',7,101,'ccccccccccccccccc','team1');
> insert into j values('bbb',8,302,'ccccccccccccccccc','team2');
> insert into j values('ccc',9,503,'ccccccccccccccccc','team2');
>
> mysql> select * from j;
> +------+------+-------+-------------------+-------+
> | proj | id   | score | cpid              | team  |
> +------+------+-------+-------------------+-------+
> | aaa  |    1 |   100 | aaaaaaaaaaaaaaaaa | team1 |
> | bbb  |    2 |   200 | aaaaaaaaaaaaaaaaa | team1 |
> | ccc  |    3 |   300 | aaaaaaaaaaaaaaaaa | team2 |
> | aaa  |    4 |   100 | bbbbbbbbbbbbbbbbb | team2 |
> | bbb  |    5 |   300 | bbbbbbbbbbbbbbbbb | team1 |
> | ccc  |    6 |   400 | bbbbbbbbbbbbbbbbb | team1 |
> | aaa  |    7 |   101 | ccccccccccccccccc | team1 |
> | bbb  |    8 |   302 | ccccccccccccccccc | team2 |
> | ccc  |    9 |   503 | ccccccccccccccccc | team2 |
> +------+------+-------+-------------------+-------+
> 9 rows in set (0.00 sec)
>
> mysql> select cpid,sum(score),team from j group by cpid;
> +-------------------+------------+-------+
> | cpid              | sum(score) | team  |
> +-------------------+------------+-------+
> | aaaaaaaaaaaaaaaaa |        600 | team1 |
> | bbbbbbbbbbbbbbbbb |        800 | team2 |
> | ccccccccccccccccc |        906 | team1 |
> +-------------------+------------+-------+
> 3 rows in set (0.00 sec)
>
> Using MAX or MIN on the team gives different but not necessarily closer
> results.
>
> mysql> select cpid,sum(score),max(team) from j group by cpid;
> +-------------------+------------+-----------+
> | cpid              | sum(score) | max(team) |
> +-------------------+------------+-----------+
> | aaaaaaaaaaaaaaaaa |        600 | team2     |
> | bbbbbbbbbbbbbbbbb |        800 | team2     |
> | ccccccccccccccccc |        906 | team2     |
> +-------------------+------------+-----------+
> 3 rows in set (0.00 sec)
>
> mysql> select cpid,sum(score),min(team) from j group by cpid;
> +-------------------+------------+-----------+
> | cpid              | sum(score) | min(team) |
> +-------------------+------------+-----------+
> | aaaaaaaaaaaaaaaaa |        600 | team1     |
> | bbbbbbbbbbbbbbbbb |        800 | team1     |
> | ccccccccccccccccc |        906 | team1     |
> +-------------------+------------+-----------+
> 3 rows in set (0.00 sec)
>
> Given that for cpid = 'bbbbbbbbbbbbbbb', they have 2 rows where it is team1,
> and only 1 with team2 but the original query gives team2 and rightly so as
> it just uses the first row in mysql's slightly illegal (but useful!) use of
> allowing other columns in the query but not in the group by.
>
> The question is, is there any way to modify this query so that it would
> return the team having the most entries?
>
> Theoretical what I would like:
>
> | cpid              | sum(score) | team  |
> +-------------------+------------+-------+
> | aaaaaaaaaaaaaaaaa |        600 | team1 |
> | bbbbbbbbbbbbbbbbb |        800 | team1 |
> | ccccccccccccccccc |        906 | team2 |
>
>
> If not, is there an easy way to have another column, say mostteam char(10)
> and run an update statement on the whole table which would put the correct
> value in?
>
> Regards
>
> Phil
>
>   
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - http://www.avg.com 
> Version: 8.0.176 / Virus Database: 270.10.4/1880 - Release Date: 1/7/2009 8:49 AM
>
>   

--------------040808040405000808020509--