Re: [SQLQueriesNoCode] Selecting a count based on different columns
AFAIK Mysql doesn't support nested queries (only the latest version, but, it
is still in pre-release mode)
Also, i've found a few "bugs" with the GROUP BY function of MySQL, it
doesn't always work...
Try some newsgroup at Mysql.com .... they're really great guys and help a
(sorry if i don't help much...)
Sergio Coelho Charrua
@ : scoelho@...
url : www.flesk.com
----- Original Message -----
From: "thomas_pickert" <Thomas@...>
Sent: Tuesday, July 15, 2003 4:23 PM
Subject: [SQLQueriesNoCode] Selecting a count based on different columns
> I'm new to this group, so please be gentle. :-)
> I'm maintaining a game which is using a MySQL database. This game is
> based on players who have (several) characters which are living in
> different locations.
> I now want to find out which players have more than 2 characters in
> one location. I could do that using a script, of course, but I have
> the vague feeling that it should be possible to do it in a SQL query.
> The table 'chars' has int columns 'player' (which is the associated
> player for the character), 'location' (the character's location)
> and 'id' (which is the character's unique id).
> My first approach was:
> SELECT player FROM chars GROUP BY location, player HAVING count(*) >
> But that one only counts the characters for each location, always
> resulting in a count > 2. That means, that this query simply lists
> all players.
> I somehow need to tell count that it should only count the characters
> for a particular player per location. Is something like a nested
> aggregate function possible? I haven't found out anything about that
> I would appreciate any help. Also the hint, that is actually NOT
> possible to do it in a query is welcome. And don't refrain from
> telling me that this is the wrong forum either.
> Regards, Thomas.
> To unsubscribe from this group, send an email to:
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/