function sum() 2 fields with same id

Question

I am trying to get the sum of particular field. I am getting the SUM wrong when I try to get the values together. I have this:

• reference: id_skill, user, level_paid

More specifically the table reference as:

id_skill	user	level_paid
100	Red	1
100	Red	1
200	Red	2
200	Red	1

What I want as a result from my query is,

id_skill	user	level_paid
100	Red	2
200	Red	3

SELECT   
  SUM(level_paid) AS total,
FROM reference
WHERE user = 'Red'
GROUP BY 
  id_skill

What I am trying to achieve is to get the sum of the field "level_paid" if the records of "id_skill" is more than 1.

Answer 1

You can achieve the result with the following query. The HAVING clause will ensure, you are only summing the values, which are more than 1 records, based on the GROUP BY condition e.g. at least two id_skill with the same value.

SELECT
  id_skill,   
  user,
  SUM(level_paid) AS level_paid
FROM reference
WHERE user = 'Red'
GROUP BY 
  id_skill, user
HAVING COUNT(*) > 1