The needs:

1. get a list with the names of the students that paid for a book in 2024
2. get the total they paid for their book(s)

I have the following request:

SELECT id_student, name_student, date_insc_student,  payment_student
FROM tblstud
LEFT JOIN 
( SELECT SUM(amount_payment) AS totalpay, idstud_payment FROM $t_p tblpay GROUP BY idstud_payment ) AS tblpay 
ON tblpay.idstud_payment = tblstud.id_student 
WHERE year(tblstud.date_insc_student) = 2024 
ORDER BY date_insc_student, name_student 

I get the data I need and read line by line, but there is no totalpay in the result. How do I get the total amount of what each student paid for his books?

I've read some SO questions such as this and this, and some others, but no clue. What am I doing wrong/missing?

I can also use another differently structured query if needed.

The needs:

1. get a list with the names of the students that paid for a book in 2024
2. get the total they paid for their book(s)

I have the following request:

SELECT id_student, name_student, date_insc_student,  payment_student
FROM tblstud
LEFT JOIN 
( SELECT SUM(amount_payment) AS totalpay, idstud_payment FROM $t_p tblpay GROUP BY idstud_payment ) AS tblpay 
ON tblpay.idstud_payment = tblstud.id_student 
WHERE year(tblstud.date_insc_student) = 2024 
ORDER BY date_insc_student, name_student 

I get the data I need and read line by line, but there is no totalpay in the result. How do I get the total amount of what each student paid for his books?

I've read some SO questions such as this and this, and some others, but no clue. What am I doing wrong/missing?

I can also use another differently structured query if needed.

• sql
• mysql

• 5 You don't have totalpay in your select statement. – aynber Commented Feb 6 at 15:35
• 1 @aynber: I have it in the query, but where should I put it then? – BookKeeper Commented Feb 6 at 15:39
• It's good that you have presented the query, but the code that actually causes the diagnostic message is essential to the question. That's probably not the $pdo->prepare (unless there's another form of missing context), but rather the code that attempts to read the results, especially the part that tries to read the (missing) totalpay column. – John Bollinger Commented Feb 6 at 15:40
• 1 In your select. If the $t_e table doesn't have a column named totalpay, then you can just add it after payment_student. If it does, then you'll need to use the table alias tblpay.totalpay after payment_student. – aynber Commented Feb 6 at 15:40
• 1 A simpler alternative: SELECT id_student, name_student, (SELECT SUM(amount_payment) FROM tblpay WHERE idstud_payment = id_student) AS totalpay FROM tblstud WHERE year(date_insc_student) = 2024 – Olivier Commented Feb 6 at 16:03

2 Answers 2

If you want all students, regardless of whether they paid for 2024, you can keep the LEFT JOIN and do:

SELECT s.id_student, s.name_student, s,date_insc_student, s,payment_student,
       SUM(tblpay.amount_payment) as totalpay
FROM tblstud s
  LEFT JOIN FROM $t_p tblpay on tblpay.idstud_payment = s.id_student and year(tblstud.date_insc_student) = 2024
GRPUP BY s.id_student
ORDER BY s.date_insc_student, s.name_student

If you want only those who paid for 2024, you can do:

SELECT s.id_student, s.name_student, s,date_insc_student, s,payment_student,
       SUM(tblpay.amount_payment) as totalpay
FROM tblstud s
  JOIN FROM $t_p tblpay on tblpay.idstud_payment = s.id_student 
WHERE year(tblstud.date_insc_student) = 2024
GRPUP BY s.id_student
ORDER BY s.date_insc_student, s.name_student

totalpay is available from the joined query, you can add it to your select list:

SELECT id_student, name_student, date_insc_student,  payment_student, totalpay
-- Here --------------------------------------------------------------^
FROM tblstud
LEFT JOIN 
( SELECT SUM(amount_payment) AS totalpay, idstud_payment FROM $t_p tblpay GROUP BY idstud_payment ) AS tblpay 
ON tblpay.idstud_payment = tblstud.id_student 
WHERE year(tblstud.date_insc_student) = 2024 
ORDER BY date_insc_student, name_student