[RESOLVED] Using SUM() function in query?

[JJohnsenDK]

Hey

I want to get the sum of this database:

id: 1 - goal - 1
id: 2 - goal - 1
id: 3 - goal - 1
id: 4 - goal - 1

So all the 1 numbers should be plused together so i print 4. I use the SUM() function in my query, but i get this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Programmer\xampp\xampp\htdocs\test\test\pl_details2.php on line 20

And im using this code:


<?php
include('config.php');

$sql = "SELECT actions.players_ID, SUM(actions.goal) AS goals, actions.game_ID, player.fname, player.lname
FROM actions
INNER JOIN player ON player.player_id = ".$_GET['id']."";

$result = mysql_query($sql);
$result2 = mysql_query($sql);

$row = mysql_fetch_array($result);
?>

<table>
<tr>
<td>Navn:</td>
<td><?=$row['fname']." ".$row['lname']; ?></td>
</tr>
<tr>
<td>Kampe:</td>
<td><?=$row['matchs']; ?></td>
</tr>
<tr>
<td>Mål:</td>
<td>
<?=$row['goals']; ?>
</td>
</tr>
</table>

[NogDog]

Well, the error message would indicate that MySQL could not parse or otherwise process your query. Try adding some debugging to your mysql_query() command:

$result = mysql_query($sql) or die("Query failed ($sql) " . mysql_error());

[NightShift58]

You're missing GROUP BY 1 in your SQL statement.

Also, do you need $result = mysql_query($sql);and$result2 = mysql_query($sql);back to back?

[JJohnsenDK]

Okay.. I now tried this GROUP BY 1 but it doesnt quite work. The warning msg disapreas but it only prints 1 where it should print 4. My quess is that it aint plusing the four 1 numbers?

And i removed the $result2, yes it was back to back...

[NogDog]

What exactly do you want a sum of: number of goals in a given game, number of goals by a given player in an entire season, etc.? That will determine what column(s) you want to group by.

[NightShift58]

Not quite... The query, as it is written, only makes sense if GROUP BY 1 (or actions.players_ID) is used. What the query is returning is a list of players, with the numbers of goals they respectively scored SUMmed, which is what the output is displaying (i.e. "fname", "lname").

One would need to add a WHILE loop (WHILE ($row = mysql_fetch_array($result)) {) to display all the players and the goals they scored. As the script cycles through the WHILE loop, a "grand total" counter could be incremented with individual scores.

Or - a new script...

[NogDog]

That's why I'm asking...it doesn't really make sense as currently stated; so rather than trying to fix the existing query, I want to understand the exact requirement so that we can come up with a query that addresses that requirement.

(You'd be amazed - or maybe not - at the number of bugs in major applications that can have their ultimate causes traced back to incorrect or incomplete requirements gathering and analysis. :) )

[JJohnsenDK]

Im looking for a players individually total grand score of a season. I have these tables:

Actions:
game_ID: 1 - Players_ID: 1 - goal - 1
game_ID: 1 - Players_ID: 1 - goal - 1
game_ID: 1 - Players_ID: 2 - goal - 0
game_ID: 1 - Players_ID: 3 - goal - 1
game_ID: 2 - Players_ID: 1 - goal - 1

Here player one have scored 2 goals in first game and 1 goal in second game, player two 0 goals and player three 1 goal. What i want then is for example is to show all the goals player one have scored, which would be 3.

I dont know if i explained myself good enongh?

[NightShift58]

Sorry... The answer was really geared towards JJohnsenDK - something like "thinking out loud".

I wasn't doubting whether you'd be able to fix this, and I doubt that you would have gotten in this mess in the first place.

[NogDog]

How about:

$sql = "SELECT SUM(goal) AS goals FROM actions WHERE players_ID = ".$_GET['id'];

[JJohnsenDK]

That works! thanks alot, but cant i JOIN the player tables in the same query? or do i have to make a new query to get player table?

[NogDog]

$sql = "SELECT actions.players_ID, SUM(actions.goal) AS goals, player.fname, player.lname
FROM actions INNER JOIN player ON player.player_id = actions.players_ID
WHERE actions.players_ID = ".$_GET['id']." GROUP BY actions.players_ID";

(I'm not sure if the GROUP BY clause is strictly necessary in this case or if it's implied byt the SUM() function, but it shouldn't hurt to include it.)