Page 1 of 1
Summing Rows DB [Resolved]
Posted: Sun Dec 15, 2013 5:26 am
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;
Re: Summing Rows DB
Posted: Sun Dec 15, 2013 6:19 am
by a_bertrand
SELECT SUM(user_coins) AS TOTAL FROM users
Re: Summing Rows DB
Posted: Sun Dec 15, 2013 6:26 am
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;
}
Re: Summing Rows DB
Posted: Sun Dec 15, 2013 6:51 am
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;
}
Re: Summing Rows DB
Posted: Sun Dec 15, 2013 6:59 am
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!!!
Re: Summing Rows DB [Resolved]
Posted: Mon Dec 16, 2013 6:38 am
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)
?
Re: Summing Rows DB [Resolved]
Posted: Mon Dec 16, 2013 4:34 pm
by a_bertrand
The query is certainly wrong. SUM works only for grouping... not for each row...
Re: Summing Rows DB [Resolved]
Posted: Tue Dec 17, 2013 1:08 am
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.
Re: Summing Rows DB
Posted: Tue Dec 17, 2013 12:01 pm
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
Re: Summing Rows DB
Posted: Tue Dec 17, 2013 12:09 pm
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 ";