SomeoneS
SomeoneS

Reputation: 1279

How to sum result of query

I have one query where the output processing looks like this:

 while($row = mysql_fetch_array($result)){
    echo $row['username'] . " " . $row['earning'];
 }

And outputs result is like this:

 JOHN  200
 JOHN  350
 NEO   100
 NEO   220

What I want to achieve is that every name appears once, and "earning" is sum of all earnings of that name, like this:

 JOHN 550
 NEO  320

I have to mention that I CANNOT change the query; that is biggest problem.

Is there any hope? Some suggestions? :)

Upvotes: 0

Views: 207

Answers (5)

M_A_K
M_A_K

Reputation: 378

try this in mysql side you don't need to calculate in php side so don't change your PHP side code jst change query like this

SELECT
  username,
  SUM(earning)
FROM yourtable
GROUP BY (username)

hope its will work for you

Upvotes: 0

mgraph
mgraph

Reputation: 15338

try:

$user = array();

while($row = mysql_fetch_array($result)){
    $user[$row['username']]  += $row['earning'];
 }

to do echo :

foreach($user as $key => $value) {
    echo $key."=". $earnings."<br>";
}

Upvotes: 4

Kartik
Kartik

Reputation: 9873

To get a quick solution to this answer you may want to simply append these results to an associated array and then simply loop over it to get the final count.

So, something like this:

 $names= array();

 while($row = mysql_fetch_array($result)){
    $names[$row["username"]] += $row["earning"];
 }

 foreach($names as $k => $v) {
    echo $k." ".$v."\n";
 }

Upvotes: 1

drew010
drew010

Reputation: 69977

You can sum the values in the loop to another array and then output it.

Try:

$earnings = array();

while($row = mysql_fetch_array($result)) {
    if (!isset($earnings[$row['username']])) $earnings[$row['username']] = 0;
    $earnings[$row['username']] += $row['earning'];
}

foreach($earnings as $user => $earnings) {
    echo "$user  $earnings<br />\n";
}

Upvotes: 4

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324790

Instead of just selecting the data, select the field and SUM(otherfield) with otherfield being the field with the number to be summed. Then add GROUP BY with the first field.

Upvotes: 0

Related Questions