帮助SQL SUM（）？

Question

I am not sure if I am supposed to use SUM() for this, but I wanted to get the sum of all the values in the "earnedcoins" column and this is my code:

$sql = "SELECT SUM(earnedcoins) FROM users";
$result = mysqli_query($conn, $sql);
$earnedcoins = mysqli_num_rows($result); 
echo $earnedcoins;
$conn->close();

Unfortunately, it just stamps out number 1. Can anybody help me resolve this issue? Any help will be appreciated.

Answer 1

mysqli_num_rows() will always return 1 for that query, as you only get one row returned - which is the number of summed rows. Instead, you need to fetch that value from SUM().

Give the SUM() an alias, and fetch the value. You should also use GROUP BY when using aggregate functions like SUM() - I don't know your table-structure, but perhaps GROUP BY id if that exists.

$sql = "SELECT SUM(earnedcoins) as result FROM users";
$result = mysqli_query($conn, $sql);
$sum = $result->fetch_assoc()['result'];
echo $sum;
$conn->close();