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=11502.10531.6
