I have a table which tracks referral commission for students referring other students to upcoming courses.
I need a query or PHP to calculate the students commissions.
Baiscally in the students dashboard they have a section where they can see what their commissions have been.
This is what the table looks like:
Refpaid is a simple Y or N value indicating whether that referral was paid out to the student.
I need a query that will tell me the following.
Query records where refbyid = session variable[‘refid’] (this I can do) This will be for the entire query as the student only needs to see the referrals he has referred
Then I need to know
1.) Total amount of all referrals in the query (SUM?) using the ref amount column refamount
2.) Total amount (SUM) of all referrals amounts where PAID = yes (summing up refamount column)
3.) Total amount (SUM) of all referrals where PAID = no. (summing up refamount column)
4.) Record count of all referrals
5.) Record Count of all referrals where paid = yes
6.) Record Count of all referrals where paid = no
Not sure whether to do the math in PHP or even how to structure in MySQL.
Either way I need help building the query.
I’ma take a stab, and then someone more knowledgable will do it better.
SUM(Refamount) AS TotalRef,
SUM(CASE refpaid WHEN 'Y' THEN Refamount ELSE 0 END CASE) AS TotalYes,
COUNT(Refamount) AS CountRef,
SUM(CASE refpaid WHEN 'Y' THEN 1 ELSE 0 END CASE) AS CountYes
GROUP BY Refbyid
(If doing it for a specific student, add WHERE Refbyid = <studentsID> before the group by )
Note: Number and total of ‘nos’ is a derived value (Total - Yes = No) and so should not be queried for sake of brevity.
SUM(refamount) AS TotalRef,
SUM(CASE WHEN refpaid ='Y' THEN refamount ELSE 0 END CASE) AS TotalYes,
COUNT(refamount) AS CountRef,
SUM(CASE WHEN refpaid='Y' THEN 1 ELSE 0 END CASE) AS CountYes
GROUP BY refbyid
Here is the result.
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE) AS TotalYes, COUNT(refamount) AS CountRef, SUM(CASE WHEN refpaid='Y' THEN ' at line 3