Rosamunda Rosamunda - 3 months ago 5
MySQL Question

How to sum() values in mySQL that are related to a specific ID?

I want to show in a single table all payments made by students.
Students can belong to more than one course, so I would need to know the sum of payments that belongs to each course.

I've tried this:

SELECT usuarios.userID, usuarios.userEmail, usuarios.docVerificada,
pagos.cursoID, pagos.pagoMonto,
SUM(pagos.pagoMonto)

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

WHERE pagos.estadoPago = 1

GROUP BY usuarios.userEmail


But it outputs all payments, one for each row, but I want to group them according to their cursoID.

These are the tables involved:



CREATE TABLE pagos (
pagoID int unsigned not null auto_increment primary key,
userID int not null,
cursoID int null,
estadoPago int(1) not null DEFAULT '0'
pagoMonto int null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE usuarios(
userID int unsigned not null auto_increment primary key,
userEmail char(50) null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


This is the actual output that I get:
enter image description here

And these are the results without any grouping or sum:
enter image description here

The intended results would be:


UserID 16 -- cursoID 15 -- payments total 23

UserID 16 -- cursoID 16 -- payments total 77 (it should sum only 24,
26 and 27)


and so on...

Answer

You should include pagos.cursoID in Group By in your query

SELECT      
    usuarios.userID, usuarios.userEmail, usuarios.docVerificada, 
    pagos.cursoID, pagos.pagoMonto, SUM(pagos.pagoMonto) as Total
FROM 
   usuarios 
INNER JOIN 
   pagos
ON 
   usuarios.userID = pagos.userID
WHERE 
    pagos.estadoPago = 1
GROUP BY 
    usuarios.userID, pagos.cursoID
Comments