mysqli every time work only else condition but not

问题:

Here Is My Query

CREATE VIEW marksheet as
SELECT name as name, student_id as student_id, 
roll as roll, class as class,exam_year as exam_year, 
subject_name as subject, exam_type as exam_type,
sum(full_mark) as full_mark, sum(getmark) as getmark,
department as department,
IF(SUM(IF(gpa='f' OR gpa='F',-9999,gpa))>=0, 
CAST(IF(subject_type=1,SUM(gpa)-2/count(subject_name),SUM(gpa)/count(subject_name)) 
AS CHAR), 'F') as total_gpa
FROM mark
GROUP by roll, class, exam_type

Not work

IF(subject_type=1,SUM(gpa)-2/count(subject_name),SUM(gpa)/count(subject_name))

Every time work only else condation SUM(gpa)/count(subject_name

Not work subject_type=1,SUM(gpa)-2/count(subject_name)

My Table

Result : gpa = 5+8+4+6

         = 23

But subject_type = 1 so ,minus -2 (not work)

         = 21 (Not work)

Final Gpa = 21/count(subject_name)

回答1:

If there can be only one extra subject (i.e. subject with subject_type=1) and all other subjects have subject_type=0, you can use MAX(subject_type) to determine if a student took an extra subject. This query should do what you want (note that you also need () around SUM(gpa)-2):

CREATE VIEW marksheet as
SELECT name as name, student_id as student_id, 
roll as roll, class as class,exam_year as exam_year, 
subject_name as subject, exam_type as exam_type,
sum(full_mark) as full_mark, sum(getmark) as getmark,
department as department,
IF(SUM(IF(gpa='f' OR gpa='F',-9999,gpa))>=0, 
CAST(IF(MAX(subject_type)=1,(SUM(gpa)-2)/count(subject_name),SUM(gpa)/count(subject_name)) 
AS CHAR), 'F') as total_gpa
FROM mark
GROUP by roll, class, exam_type

I've created a simplified demo at SQLFiddle.