Adding up variables in mysql

Question

I own an rpg and want to add up all the levels of the users monster and get an end number.

So I narrowed it down for one user and works great.

$_SESSION['username'] = "smbisme";


$query = "SELECT belongsto, SUM(level) FROM user_pokemon WHERE `belongsto` = '".$_SESSION['username']."' "; 

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
    echo $row['belongsto'];
    echo "". $row['SUM(level)'];
    echo "<br />";
}

Now, that shows and works for the user 'smbisme' but now I want to do it for all users.

So I try and take the where out like so.

$query = "SELECT belongsto, SUM(level) FROM user_pokemon  "; 

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
    echo $row['belongsto'];
    echo "". $row['SUM(level)'];
    echo "<br />";
}

But then for some reason i am only getting the highest result ( 1 result ) i would like to do what i did for the 1 user but grab all of the results instead of just the one user.

I'd choose username: '; drop table user_pokemon; --. In another words use prepared statements otherwise there's security hole. — Leri
– Leri, Commented Sep 13, 2012 at 12:40

Fluffeh · Accepted Answer · 2012-09-13 12:47:11Z

1

You need to use a group by statement for multiple row aggregates:

SELECT 
    belongsto, 
    SUM(level) 
FROM 
    user_pokemon 
group by 
    belongsto

On that note, as PLB points out in the comments, this code is rather vunlerable to a code injection. You should consider using a prepared statement instead to get this result. No injections, it's a good thing.

To order by a column or aggregate:

SELECT 
    belongsto, 
    SUM(level) as sumLevel 
FROM 
    user_pokemon 
group by 
    belongsto 
order by 
    sumLevel desc

edited Sep 13, 2012 at 12:47

answered Sep 13, 2012 at 12:39

Fluffeh

33.6k16 gold badges69 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Leri Over a year ago

@TarikFilipovic Welcome on SO, you should accept this question as a solution so others can see it later. You'll be able to do it in 15 minutes.

Tarik Filipovic Over a year ago

Is there any way to order it by the highest Sum(level) ?

Tarik Filipovic Over a year ago

Now its no echoing out the sumLevel .... It echos out the belongsto but not the SUM(level) in the while...

Fluffeh Over a year ago

@TarikFilipovic Yeah, the column name changed by to the alias on the column/aggregate.

Tarik Filipovic Over a year ago

yer i noticed i just echo out sumLevel instead ot the SUM(level) which iw as be for and works great. Thanks again.

Aelios · Accepted Answer · 2012-09-13 12:49:49Z

1

SUM is an aggregate function so you need a group by

SELECT belongsto, SUM(level) as sumlevel FROM user_pokemon group by belongsto order by sumlevel DESC

edited Sep 13, 2012 at 12:49

answered Sep 13, 2012 at 12:39

Aelios

12.2k2 gold badges38 silver badges54 bronze badges

1 Comment

Tarik Filipovic Over a year ago

Is there any way to order it by the highest Sum(level) ?

Collectives™ on Stack Overflow

Adding up variables in mysql

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related