Hello All,
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.
SELECT
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
FROM Referals
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.
why? because it’s based on equality, and you can’t easily change it to include a test for NULL, because NULL isn’t equal to anything
i prefer CASE WHEN condition –
CASE WHEN refpaid='Y' THEN...
if the column has multiple values then you do have to repeat the column name for every test, but if there are multiple values, they should probably be defined in a table and you should be doing a join
also, when counting, i don’t like SUM( 1 or 0 ), i prefer COUNT( value or NULL )
SELECT
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
FROM referals
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
(the error message tells you ~exactly~ where to look for the problem, and digging in the manual for the expression it died on almost always solves the problem)
