How to select data from results of a left join

I have a query that returns three columns correctly. I can't figure out how to add开发者_开发技巧 a frouth column that SUM()s the data using only the results of the LEFT JOIN.

Here is the original query that returns the correct three columns:

select TITLES.NAME, 
COUNT(progskills.skill_no) as B, 
COUNT(ss.skill_no) as A,
SUM(ss.score) as C
FROM TITLES 
inner join progskills   
    on progskills.program_no = titles.title_no
left join STUSKILLS ss
    on  progskills.SKILL_NO = ss.skill_no 
    and ss.STUDENT_NO = '1234'
where progskills.program_no in (select progskills.program_no 
                                from PROGSKILLS, STUSKILLS 
                               where progskills.SKILL_NO = stuskills.SKILL_NO
                                 and stuskills.SCORE != 0
                                 and stuskills.STUDENT_NO = '1234')
group by titles.name

which correctly returns the following results:

                          B   A     C 
Database Administration   4   1     50
Implementation Specialist 4   1     50
Office Manager            4   2    130
Registrar                 4   2    130
Scheduler                 4   3    130
Scheduling Assistant      2   1     50
Support Systems Manager   4   2    130

What I am trying to do, in a fourth column, is to SUM a column named MINSCORE from the PROGSKILLS table based on the results of the left join on STUSKILLS. In other words, I only want to sum for those elements that are referenced by the left join.

I have tried many attempts, but all seem to either fail syntax or cause the other data column results to multiply.

My best guess, is to add the following to my primary SELECT clause...

SUM(select ps.minscore from PROGSKILLS ps where ps.SKILL_NO = ss.skill_no) as D

But SQL complains that the SUM(SELECT is a syntax error and the ss.skill_no cannot be bound.

I know that SUM(SELECT is acceptable.

I realize this is a very complex query but I hope someone can help me figure out how to get the data from PROGSKILLS based on the elements counted in column "A".

In advance ... THANK YOU!

Did you try this:?

sum(case when ss.skill_no is null then 0 else progskills.MINSCORE end) as D