0

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.

5
  • At this point of your sql all result rows will get the same SUM(pagos.pagoMonto) AS total data, you have to rething your sql logic Commented Mar 7, 2017 at 16:07
  • Not sure but test GROUP BY usuarios.userID, pagos.userID Commented Mar 7, 2017 at 16:09
  • Sry, forget all of my comments, didnt get it right. Commented Mar 7, 2017 at 16:10
  • Are you sure that the pagos entry is not duplicated ? I mean maybe there are two rows for same userID and same payment ? Can we see the result of SELECT * FROM pagos WHERE userID = 1; please ? Commented Mar 7, 2017 at 16:10
  • Thanks for your comment @JazZ. Just in case I've checked the payments, and the output is the expected two rows with just the 100 + 50 payments in it. Commented Mar 7, 2017 at 16:12

1 Answer 1

1

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

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.