Sum and case returning too many rows

[Torrent Girl]

hello all

I tried my hardest to figure this one out by myself, but here goes.

I have a query that returns sums of a few columns. I need to count the number of people with certain occupations and count the number of classes they took by a certain course ID.

The problem is there are only 4 people. 1 of the took 9 courses, the other 3 too none.

Because of the 9 courses, the query is showing 9 teachers versus 4.

I know that the problem is I need to somehow not included the course id or I am grouping the wrong way. Grouping by courseID won't work as there are 9 different courses id's for the one user.

here is my query: any suggests are appreciated as i have been working on this all night and is has to be done by 8 am.



SELECT    
p.Program_name,
p.program_ID,
p.Program_City,
SUM(CASE p.occupationID WHEN '4' THEN 1 ELSE 0 END) AS teacher,
SUM(CASE p.occupationID WHEN '1003' THEN 1 ELSE 0 END) AS leadTeacher,
SUM(CASE p.participant_ladder_level WHEN 1 THEN 1 ELSE 0 END) AS level1,
SUM(CASE p.participant_ladder_level WHEN 2 THEN 1 ELSE 0 END) AS level2,
SUM(CASE p.participant_ladder_level WHEN 3 THEN 1 ELSE 0 END) AS level3,
SUM(CASE p.participant_ladder_level WHEN 4 THEN 1 ELSE 0 END) AS level4,
SUM(CASE p.participant_ladder_level WHEN 5 THEN 1 ELSE 0 END) AS level5,
SUM(CASE p.participant_ladder_level WHEN 6 THEN 1 ELSE 0 END) AS level6,
SUM(CASE p.participant_ladder_level WHEN 7 THEN 1 ELSE 0 END) AS level7,
SUM(CASE p.participant_ladder_level WHEN 8 THEN 1 ELSE 0 END) AS level8,
SUM(CASE p.participant_ladder_level WHEN 9 THEN 1 ELSE 0 END) AS level9,
SUM(CASE p.participant_ladder_level WHEN 10 THEN 1 ELSE 0 END)AS level10,
SUM(CASE p.participant_ladder_level WHEN 11 THEN 1 ELSE 0 END) AS level11,
SUM(CASE p.participant_ladder_level WHEN 12 THEN 1 ELSE 0 END) AS level12,
SUM(CASE p.participant_ladder_level WHEN 13 THEN 1 ELSE 0 END) AS level13,
SUM(CASE p.participant_ladder_level WHEN 14 THEN 1 ELSE 0 END) AS level14,
SUM(CASE p.participant_ladder_level WHEN 15 THEN 1 ELSE 0 END) AS level15,
SUM(CASE WHEN courseTypeID IN ('18', '19') AND participant_ladder_level IN ('11', '12') THEN 1 ELSE 0 END) AS teacherCertificate1,
SUM(CASE WHEN courseTypeID IN ('18', '19') AND participant_ladder_level IN ('13', '14', '15') THEN 1 ELSE 0 END) AS teacherCertificate2
FROM  dbo.REPORT_PROGRAMS_WITH_PARTICIPANTS p
LEFT OUTER JOIN dbo.ccac_user_courses c ON p.participant_ID = c.userID
LEFT OUTER JOIN  dbo.ccac_courses cc ON c.courseID = cc.courseID
WHERE     (p.Program_sde_funded = 1)
AND (p.Occupation_in_program IN ('Lead teacher', 'Classroom Teacher'))
AND (p.program_ID = 194)
GROUP BY p.program_ID, p.Program_name, p.Program_City
ORDER BY p.Program_name, p.program_ID

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3141
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6