Mind Rapist Mind Rapist - 4 months ago 10
SQL Question

How can I get a total value from the same column of multiple rows in a mysql query?

Good morning Stack Overflow I have a project in ASP.Net (C#) connected to a game database. I have a

player
table and I want to get the total value of the
playtime
row from all the players with the same
account_id
. I have tried some stuff I searched through the internet but I did not find anything helpful. My last logic was this:

MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
String queryStr;
String connStringPlayer = System.Configuration.ConfigurationManager.ConnectionStrings["PlayerDB"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connStringPlayer);
conn.Open();
queryStr = "SELECT playtime FROM player.player WHERE account_id='" + AccountId + "' LIMIT 5";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
{
Int32 countpt = (Int32)cmd.ExecuteScalar();
String playcount = countpt.ToString();
AccountInfoTotalGameMinuetsValue.Text = playcount;
}
conn.Close();


In this way my
AccountInfoTotalGameMinuetsValue
Label displays only the
playtime
of the 1st player of this
account_id
(2 players exist in this account)
I appreciate any tips thank you :)

Answer

SUM is what you are looking for

SUM([DISTINCT] expr)

Returns the sum of expr. If the return set has no rows, SUM() returns NULL. The DISTINCT keyword can be used to sum only the distinct values of expr.

SUM() returns NULL if there were no matching rows.

So the following query Will give you the totals for all the players

SELECT account_id, SUM(playtime) FROM player.player GROUP BY account_id;

if you want the information for just one player

SELECT account_id, SUM(playtime) FROM player.player 
WHERE account_id = some_id 
GROUP BY account_id;