Geek asks and answers

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

PROBLEM
I have four game servers that collect data. Entering data into one table. Unfortunately, this causes four entries per stat for a single player.

WHAT I WANT
I want one record and SUM () for certain columns.

I will post an expression that I wrote that does not work, but you will understand what I would like to do.

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;
+3
source share
2 answers

Let me start by saying that duplicate rows in your database are truly less than ideal. The database is fully normalized, making the data much easier to manipulate without causing random anomalies.

, , , dweiss ( ):

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
;

, , characterID, , . .

+5

, (, , , ). , : max, sum, !

+5

More articles:


All Articles