Counting multiple fields

[Phailak]

Hail,

I have a database that stores some stats for a hockey league in the following manner:

Field names are GP, G, A

Whenever a player Plays a game, his ID is added to the column GP while other columns go to 0
If a player scores a goal, his ID goes under G, the guy who assisted goes under A

What I want is a query that can give me the count for each ID that shows up in GP for GP, G and A

Is there an easy way to do this with just one statement? Right now, it's really messy since I have to loop through each player ID in GP and make individual queries to count GP, G and A because I can't figure out an easier way and I don't want to rebuild the DB

[timgraham]

I'm not 100% sure what u mean and I'm no expert myself but I did just finish writing a querry that i think may work for you. You need to use "SUM" and "GROUP BY". This will give you the SUM of a field and GROUP BY whatever field you prefer. Actualy after thinking a bit more you may need to use "COUNT" and "GROUP BY". Do a google search on "MYSQL, COUNT, SUM" and read up on it. Sorry if I have confused you, I'm new to this.

$KTColParam1_purchase_add_credit = "0";
if (isset($_GET["cm"])) {
$KTColParam1_purchase_add_credit = (get_magic_quotes_gpc()) ? $_GET["cm"] : addslashes($_GET["cm"]);
}
mysql_select_db($database_mx_shop, $mx_shop);
$query_purchase_add_credit = sprintf("SELECT orderdetail_ode.idord_ode, sum(products_prd.credits) AS sum_credits_1 FROM (orderdetail_ode LEFT JOIN products_prd ON products_prd.name_prd=orderdetail_ode.pname_ode) WHERE orderdetail_ode.idord_ode='%s' GROUP BY orderdetail_ode.idord_ode ", $KTColParam1_purchase_add_credit);
$purchase_add_credit = mysql_query($query_purchase_add_credit, $mx_shop) or die(mysql_error());
$row_purchase_add_credit = mysql_fetch_assoc($purchase_add_credit);
$totalRows_purchase_add_credit = mysql_num_rows($purchase_add_credit);

[timgraham]

Have a look at this thread

http://www.php-editors.com/forum/index.php...ST&f=23&t=1886&

[Phailak]

Thanks, I figured it out wit a little help, basically I have a SELECT statement in my joins where as each join does a count for each field. So that Table A Join Table A count(fieldA) on bla bla bla join again tablea but with a different field to count

Anyway all that to say it works, thanks