# Help with query involving math

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:

Refid
Refamount
Refstudentid
refbyid
Refbyid (referring student)
Refpaid
Refamount

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.

Thanks!!!

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.

may i make a semi-offtopic comment?

i don’t like CASE expression WHEN

``````CASE refpaid WHEN 'Y' THEN...
``````

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 )

Thanks Guys!

This is what I am attempting.

``````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
``````

END CASE is wrong, it should be just END

(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)

Sorry, yes, END CASE is only when it’s a statement, not an expression.

Thank you both! Learning so much here!

Personally I’d use MySQL’s IF function here

``````
SUM( IF( `refpaid` = 'Y', `Refamount`, 0 )) as `TotalYes`

``````

Nothing wrong with either method - it’s one of those YMMV things.

OP
As r937 points out, be wary of nulls. Also, if refpaid only has two values (‘yes’ or ‘no’) then 1 and 0 with default 0 should be used.

like you said, it’s one of those ymmv things

but should you ever try to write IF instead of CASE in any other database system, your mileage will be zero

CASE is standard SQL, and IF ain’t 