0

I have 3 tables: wp_names, wp_payments, wp_renewals. I'm using JOIN in student_id on all of them.

SELECT s.student_id AS 'Student ID'
    ,s.student_first_name AS 'First Name'
    ,@numberofregistrations := count(DISTINCT p.payment_id) as 'Number of Registrations'
    ,@latestregdate := date(max(p.date)) AS 'Latest Registration'
    ,@renewals := IF(count(DISTINCT r.date) >= 1, count(DISTINCT r.date), '') as Renewals
    ,@latestrenewal := date(max(r.date)) AS 'Latest Renewal'
    ,@latestpayment := IF(@renewals >= 1, @latestrenewal, @latestregdate) AS 'Latest P.'
    ,DATE_ADD(@latestpayment,INTERVAL 12 MONTH) AS 'Due Renewal'

FROM wp_names AS s
LEFT JOIN wp_payments AS p ON s.student_id = p.student_id
LEFT JOIN wp_renewals AS r ON s.student_id = r.student_id

GROUP BY s.student_id, p.payment_id, p.registration, p.date

This is the result I get. Notice that the Latest Payments are all wrong, and the Due Renewals are wrong as well.

How can I use the variables I created? I would like to use them inside the IF statement, such as:

IF(@renewals >=12, @latesrenewal, @latestregdate).

And also:

DATE_ADD(@latestpayment, INTERVAL 12 MONTH)

Results of query

And lastly, I can't figure out how to get the count(DISTINCT p.payment_id) to give the correct result of "2". Instead of this, it is doubling the student ID "2" and counting as 1 for both entries.

In summary, the two questions are:

  1. Why are the variables not working as I expected?
  2. Why is the count not counting both registrations?

I appreciate any help on this.

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
noahstime
  • 31
  • 2
  • Check out this http://stackoverflow.com/questions/16715504/mysql-define-a-variable-within-select-and-use-it-within-the-same-select and http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/ – PaulF Nov 07 '16 at 17:44
  • Short answer: don't expect session variables to work the way you want when aggregation is involved. – Uueerdo Nov 07 '16 at 17:54

0 Answers0