CODEWITHSUNDEEP
phpmysql
Anriëtte Myburgh
Anriëtte Myburgh

Reputation: 13517

MySQL GROUP BY - get items in that particular group

I am using the MySQL GROUP BY function, and want to know if there is a way to get the number of items for that group without having to a query again?

$homePointsPlayerResult = mysql_query("SELECT `player_id` FROM `conversions` WHERE `game_id` = '$game_id' AND `team_id` IS NULL GROUP BY `player_id`");

while ($players_with_points_conversions[] = mysql_fetch_row($homePointsPlayerResult)) {
    if (array_search($players_with_points_conversions['player_id'],$home_players,true) == FALSE) {
        $home_players['player_id'] = $players_with_points_conversions['player_id'];
        $home_players['conversions'] = {WANT NUMBER OF ELEMENTS FOR THIS GROUP};
    }
}

I would really appreciate your answers. Thanks in advance.

Upvotes: 0

Views: 183

Answers (2)

Greg
Greg

Reputation: 321698

You can get the COUNT:

SELECT `player_id`, COUNT(*) AS conversions FROM `conversions` WHERE `game_id` = '$game_id' AND `team_id` IS NULL GROUP BY `player_id`

Upvotes: 3

Jason Musgrove
Jason Musgrove

Reputation: 3583

Just add another column to the SELECT list along the lines of: "count(player_id) AS elementCount"

Upvotes: 0

Related Questions