为什么我在MySQL中使用SUM（）时会得到重复的值？

Question

I have a database for a school, and I want to list all users that belong to a specific course, and show the total amount that each student have pay for that course.

This is the query that I'm trying to get sorted:

SELECT    
  usuarios.userID AS userid, 
  cursos.nombreCurso, cursos.cursoPrecio, 
  SUM(pagos.pagoMonto) AS total,
  cursosUsuarios.examenes, 
  GROUP_CONCAT(DISTINCT rolesUsuarios.nombreRol SEPARATOR ',') AS 'roless',
  GROUP_CONCAT(DISTINCT pagos.pagoMonto SEPARATOR ',') AS 'pagoss'

FROM usuarios LEFT JOIN pagos
ON usuarios.userID = pagos.userID

LEFT JOIN rolesUsuarios
ON pagos.userID = rolesUsuarios.userID

LEFT JOIN cursos
ON pagos.cursoID = cursos.cursoID

LEFT JOIN cursosUsuarios
ON usuarios.userID = cursosUsuarios.userID
AND pagos.cursoID = cursosUsuarios.cursoID

WHERE pagos.estadoPago = 1
AND cursos.cursoID = 84
GROUP BY usuarios.userID

These are my tables:

usuarios (users)

CREATE TABLE usuarios(
    userID int unsigned not null auto_increment primary key,
    userEmail char(50) null,
    userApellido char(50) null,
    userNombres char(20) null
    UNIQUE(userEmail)
)

cursos (courses)

CREATE TABLE cursos (
    cursoID int unsigned not null auto_increment primary key,
    cursoID_old int(6) null
    nombreCurso char(100) not null,
    cursoPrecio int(10) null
)

cursosUsuarios

CREATE TABLE cursosUsuarios (
    cursosUsuariosID int unsigned not null auto_increment primary key,
    userID int not null,
    cursoID int not null,
    examenes int not null
)

pagos (payments for courses)

CREATE TABLE pagos (
    pagoID int unsigned not null auto_increment primary key,
    userID int not null,
    cursoID int null,
    pagoMonto int null,
    estadoPago int null
)

rolesUsuarios (user Roles)

CREATE TABLE rolesUsuarios (
    rolesUsuariosID int unsigned not null auto_increment primary key,
    userID int not null,
    nombreRol char(50) not null
)

This is the output that I'm getting:

 userID -- nombreCurso -- cursoPrecio -- total -- examenes -- roless -- pagoss 

 1 ------- Curso Demo --- 600 ---------- 300 ------ NULL ---- 1,2 ----- 100,50 

 2 ------- Curso Demo --- 600 ---------- 1000 ------ NULL --- 2,3 ----- 500

As you can see, the values shown in the field SUM(pagos.pagoMonto) AS total are multiplied by two. The last column shows the individual payments.

So total for user 1 should be 150 and for user 2 should be 500.

Answer 1

I think that you miss a distinct in your SUM(), maybe try with SUM(DISTINCT pagos.pagoMonto)
I hope this will help.