SQL query: Using DISTINCT/UNIQUE and SUM() in one statement

distinct, max, sql, sql-like, sum

Solution

Let me start by saying that duplicate rows in your database is truly less than ideal. A fully normalized database makes data much easier to manipulate without having random anomalies pop up.

However, to answer your question, this is simply what dweiss said put into code (using group by):

SELECT
  name,
  MAX(Level),
  Class,
  SUM(Kills),
  SUM(Deaths),
  SUM(Points),
  SUM(TotalTime),
  SUM(TotalVisits),
  CharacterID
FROM
  Characters
WHERE
  Name LIKE '$value%' OR CharacterID LIKE '$value'
GROUP BY
  name,
  class,
  characterid
ORDER BY
  Name ASC
;

I'm assuming name, class, characterID, are all the same for each player, because that's the only way to get those values in there. Otherwise you'll have to use aggregate functions on them as well.

Problem

THE PROBLEM I have four game servers collecting data. Putting data into a single table. Unfortunately this causes four entries per stat for one player. WHAT I WANT I want one record and SUM()on certain columns. I will post a statement that I wrote which doesn't work but you'll get the point of what I would like to accomplish. ``` SELECT DISTINCT( Name ), Max(Level), Class, Sum(Kills), Sum(Deaths), Sum(Points), Sum(TotalTime), Sum(TotalVisits), CharacterID FROM Characters WHERE Name LIKE '$value%' OR CharacterID LIKE '$value' ORDER BY Name ASC; ```

Original source