MySQL - add calculated (sum) column to a report based on criteria

I'm using a MySQL-based reporting engine to generate a report of student activity from my Learning Management System. I've got the following report that lists (among other things) the student's name, the name of the course they're taking, their score, and the credit value for the course.

(Please note that I do not have direct database access; I can only write queries through the reporting interface.)

select usr.full_name 'Learner Name',
usr.email 'Email',
crs.title 'Course Name',
pjt.name 'Job Title',
usr.dept_code 'Program',
me.score 'Score',
cst.value 'Credits',
if(me.reg_status='lrn_crs_reg_cnf',ostatus.name,ostatus1.name) as 'Enrollment Status',
DATE_FORMAT(me.reg_date,'%Y-%m-%d') 'Course Enrollment Date',
if(me.reg_status='lrn_crs_reg_cnf',DATE_FORMAT(me.comp_date,'%Y-%m-%d'),'')  'Completion Date',
sorg.name 'Organization Name',
emptype.name 'Employment Type',
mgr.full_name 'Manager Name'
from slt_enrollment me
inner join slt_course_template crs on crs.id = me.course_id
left join slt_profile_list_items ostatus on me.comp_status=ostatus.code
left join slt_profile_list_items ostatus1 on me.reg_status=ostatus1.code
inner join slt_person usr on me.user_id = usr.id
left join slt_organization sorg on sorg.id=usr.org_id 
left join slt_profile_list_items pjt on pjt.code =usr.job_title
left join slt_state st on st.state_code =usr.state and st.country_code=usr.country
left join slt_person mgr on mgr.id = usr.manager_id  
left join slt_custom_fields cst on cst.entity_id = crs.id and cst.label = 'Credit Value'
left join slt_profile_list_items emptype  on emptype.code = mgr.employment_type and emptype.lang_code='cre_sys_lng_eng'
where crs.id = me.course_id order by crs.id,usr.id;

Open in new window


What I'd like to do, if it's possible, is to add a column to this report listing the Total Credits for each user on the list. Total Credits is determined by summing [cst.value] where [me.score] >= 70, for each [usr.id] (individual user).

I tried doing this by adding the line SUM(CASE WHEN me.score >= 70 THEN cst.value) = 'Total Credits', but that didn't take. Any help is greatly appreciated.
nettech4423Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
You can do this with a correlated query:
select usr.full_name 'Learner Name',
usr.email 'Email',
crs.title 'Course Name',
pjt.name 'Job Title',
usr.dept_code 'Program',
me.score 'Score',
cst.value 'Credits',
if(me.reg_status='lrn_crs_reg_cnf',ostatus.name,ostatus1.name) as 'Enrollment Status',
DATE_FORMAT(me.reg_date,'%Y-%m-%d') 'Course Enrollment Date',
if(me.reg_status='lrn_crs_reg_cnf',DATE_FORMAT(me.comp_date,'%Y-%m-%d'),'')  'Completion Date',
sorg.name 'Organization Name',
emptype.name 'Employment Type',
mgr.full_name 'Manager Name',
(SELECT SUM(cst1.value) from slt_enrolment me1 
inner join slt_course_template crs1 on crs1.id = me1.course_id
left join slt_custom_fields cst1 on cst1.entity_id = crs1.id and cst1.label = 'Credit Value'
where me1.user_id = me.user_id and me1.score >=70 ) as 'Total Credits'
from slt_enrollment me
inner join slt_course_template crs on crs.id = me.course_id
left join slt_profile_list_items ostatus on me.comp_status=ostatus.code
left join slt_profile_list_items ostatus1 on me.reg_status=ostatus1.code
inner join slt_person usr on me.user_id = usr.id
left join slt_organization sorg on sorg.id=usr.org_id 
left join slt_profile_list_items pjt on pjt.code =usr.job_title
left join slt_state st on st.state_code =usr.state and st.country_code=usr.country
left join slt_person mgr on mgr.id = usr.manager_id  
left join slt_custom_fields cst on cst.entity_id = crs.id and cst.label = 'Credit Value'
left join slt_profile_list_items emptype  on emptype.code = mgr.employment_type and emptype.lang_code='cre_sys_lng_eng'
where crs.id = me.course_id order by crs.id,usr.id;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.