Anyone, soomeone, help please
>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/cf-talk/message.cfm/messageid:313341
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
