Summing Rows DB [Resolved]

Need help with an engine or coding not on the list? Need help with a game or the website and forums here? Direct all questions here.
Post Reply
User avatar
Epiales
Posts: 1119
Joined: Thu Aug 15, 2013 1:38 am

Summing Rows DB [Resolved]

Post by Epiales »

This is only summing the first record in the database. How can I sum EVERY record?

Code: Select all

$sql = "SELECT *, SUM(user_coins) AS TOTAL FROM users";
$user_query = mysql_query($sql);
$numrows = mysql_num_rows($user_query);
while ($row = mysql_fetch_array($user_query, MYSQLI_ASSOC)) {

$total = $row['user_coins'];
echo $total;
Last edited by Epiales on Sun Dec 15, 2013 7:38 am, edited 1 time in total.
Nothing fancy, but a work in progress!

http://gameplaytoday.net
User avatar
a_bertrand
Posts: 1537
Joined: Mon Feb 25, 2013 1:46 pm

Re: Summing Rows DB

Post by a_bertrand »

SELECT SUM(user_coins) AS TOTAL FROM users
Creator of Dot World Maker
Mad programmer and annoying composer
User avatar
Epiales
Posts: 1119
Joined: Thu Aug 15, 2013 1:38 am

Re: Summing Rows DB

Post by Epiales »

a_bertrand wrote:SELECT SUM(user_coins) AS TOTAL FROM users
That didn't show anything when I made the changes. I have the below code, but only shows the first person in the database, and doesn't count the numbers in the row itself. If that made sense lol... Thanks


Code: Select all

$sql = "SELECT *, SUM(user_coins) AS TOTAL FROM users";
$user_query = mysql_query($sql);
$numrows = mysql_num_rows($user_query);
while ($row = mysql_fetch_array($user_query)) {
$sum = $row['user_coins'];
echo $sum;
}
Nothing fancy, but a work in progress!

http://gameplaytoday.net
User avatar
Epiales
Posts: 1119
Joined: Thu Aug 15, 2013 1:38 am

Re: Summing Rows DB

Post by Epiales »

Tried this way as well, but still only shows the first persons coins, and not all the coins in that row

Code: Select all

$sql = "SELECT count(*) as total, user_coins FROM users";


$user_query = mysql_query($sql);
$numrows = mysql_num_rows($user_query);
while ($row = mysql_fetch_array($user_query)) {
$sum = $row['user_coins'];
echo $sum;
}
Nothing fancy, but a work in progress!

http://gameplaytoday.net
User avatar
Epiales
Posts: 1119
Joined: Thu Aug 15, 2013 1:38 am

Re: Summing Rows DB

Post by Epiales »

Okay, final working code:

Code: Select all

$sql = "SELECT *, SUM(user_coins) FROM users having count(*) > 0 ";
$user_query = mysql_query($sql);
$numrows = mysql_num_rows($user_query);
while ($row = mysql_fetch_array($user_query)) {
echo $row['SUM(user_coins)'];

}
Thx!!!
Nothing fancy, but a work in progress!

http://gameplaytoday.net
User avatar
MikuzA
Posts: 395
Joined: Thu Aug 08, 2013 8:57 am

Re: Summing Rows DB [Resolved]

Post by MikuzA »

Epiales wrote:Okay, final working code:

Code: Select all

$sql = "SELECT *, SUM(user_coins) FROM users having count(*) > 0 ";
$user_query = mysql_query($sql);
$numrows = mysql_num_rows($user_query);
while ($row = mysql_fetch_array($user_query)) {
echo $row['SUM(user_coins)'];

}
Thx!!!
Hello,
I suspect your query is not working as intended? Just out of curiousity, can you give a few example rows from users and write a few rows what you are expecting.


If you to retrieve data with a SUM(), you should add a group by in the end, something to give the query to know how to sum the rows.
so, SELECT user_id, sum(user_coins) from users group by user_id would show:

1 - 400
2 - 300
..etc..
which is probably what you want? In order for this to work, you need to have several rows in users table with user_id.

Or do you want,
1 - TOTAL SUM OF COINS (all players combined)
2 - TOTAL SUM OF COINS (all players combined) (same amount as 1)
?
Why so serious?

Business Intelligence, Data Engineering, Data Mining
PHP, HTML, JavaScript, Bash/KornShell, Python, C#, PL/SQL
MySQL, DB2, Oracle, Snowflake
Pentaho, DataStage, Matillion, Unity3D, Blender
User avatar
a_bertrand
Posts: 1537
Joined: Mon Feb 25, 2013 1:46 pm

Re: Summing Rows DB [Resolved]

Post by a_bertrand »

The query is certainly wrong. SUM works only for grouping... not for each row...
Creator of Dot World Maker
Mad programmer and annoying composer
User avatar
Epiales
Posts: 1119
Joined: Thu Aug 15, 2013 1:38 am

Re: Summing Rows DB [Resolved]

Post by Epiales »

Actually,t he query is working perfectly...

I have a table in my database and then users that have coins... So for example...

David, 1 coin
Joshua, 10 coins
Betrand, 2 coins....

Ect....

It sums of the amount of coins that are in the row for user_coins just perfect. So it would add 1 + 10 + 2 and give me 13 coins as total. The code I have is working fine.
Nothing fancy, but a work in progress!

http://gameplaytoday.net
User avatar
MikuzA
Posts: 395
Joined: Thu Aug 08, 2013 8:57 am

Re: Summing Rows DB

Post by MikuzA »

Hello, not sure why it works but it should be what a_bertrand said if you only wish for SUM of the user_coins.
a_bertrand wrote:SELECT SUM(user_coins) AS TOTAL FROM users
Why so serious?

Business Intelligence, Data Engineering, Data Mining
PHP, HTML, JavaScript, Bash/KornShell, Python, C#, PL/SQL
MySQL, DB2, Oracle, Snowflake
Pentaho, DataStage, Matillion, Unity3D, Blender
User avatar
Epiales
Posts: 1119
Joined: Thu Aug 15, 2013 1:38 am

Re: Summing Rows DB

Post by Epiales »

MikuzA wrote:Hello, not sure why it works but it should be what a_bertrand said if you only wish for SUM of the user_coins.
a_bertrand wrote:SELECT SUM(user_coins) AS TOTAL FROM users
The problem is where I take the *, out of the Select, it shows nothing. And if I remove the count * from it, it shows nothing. The only way I could get it to count the total coins was to write it like I did.

$sql = "SELECT *, SUM(user_coins) FROM users having count(*) > 0 ";
Nothing fancy, but a work in progress!

http://gameplaytoday.net
Post Reply

Return to “Advanced Help and Support”