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)