CASE, WHEN, ELSE in a SELECT

[facarroll]

I have the following SELECT statement that does not show an error, but it's not working correctly, either. I suspect the CASE is not correct, but where?

 

$query = mysql_query("SELECT users.id, quiz.userId, quiz.family, quiz.quizTitle, quiz.userGroup, quiz.passState,					 
				 SUM(CASE WHEN quiz.passState=1 THEN quiz.passState * '".$pass."' ELSE (quiz.passState+1) * '".$fail."' END) AS total					 
				 FROM quiz, users 
				 WHERE  quiz.userIdRec = users.id 
				 AND quiz.addDate 
				 BETWEEN '".$start_date."' AND '".$end_date."' 
				 AND users.egroup='".$egroup5."' 
				 AND quiz.managerId='".$userid."' 
				 GROUP BY quiz.userId, quiz.quizTitle 
				 ORDER BY quiz.family, quiz.addDate") or die(mysql_error());

 

The values for the variables are dates and simple numbers. Nothing wrong with those. I suspect the syntax

 SUM(CASE WHEN quiz.passState=1 THEN quiz.passState * '".$pass."' ELSE (quiz.passState+1) * '".$fail."' END) AS total

. passState always has the value '1' or '0'. I  want to be able to derive a value from each which is a product of (in the first case) passState by the value in $pass. In the second case, I also want to calculate a value based on passState when it = '0', by simply adding '1' and multiplying by the value contained in the variable $fail.

 

Can anyone see what is wrong with the SELECT?

 

 

[Barand]

SUM(CASE WHEN quiz.passState=1 THEN quiz.passState * '".$pass."' ELSE (quiz.passState+1) * '".$fail."' END) AS total

 

Given that you are always multiplying by 1

 

SUM(CASE WHEN quiz.passState=1 THEN $pass ELSE $fail END) as total

[facarroll]

I should have explained the situation better. The SELECT takes an existing student result to a quiz from the database where (passState will always equal either pass (1) or fail (0).

I want to allocate a score to each result. The score can be any value for a pass and a different value for a fail.

So passState being 1 can be multiplied by $pass to get a score.

I want to be able to give a student a mark for attempting, even though he fails,

So in this event, passState can be multiplied by $fail, but because it's value is already 0, I add 1 to passState to enable the multiplication.

I hope this is clearer.

 

[facarroll]

Oh my! I've just read your post more carefully. I couldn't see the forest for the trees. I'll test this, but I think it looks good. Why didn't I see that?