Consolidate count data?

[jmj]

I have a query that counts the demographics of students in the various classes (ie: 12 students in french 1 8 females/4 males)
One problem. Sometimes a class will be made up of a couple of different courses (ie: Latin III and Latin IV may be taught in one class). I would like to consolidate these classes into one line of the totals. Right now they are showing 2 lines of data. (One for Latin III and one for Latin IV).
Is this possible? Below is my query that runs the counts. There are 3 fields that need to be the same for it to be a consolidated class: the school, the beginperiod, and the tchrname.



Here's what I have so far.
Select school,beginperiod, endperiod, term, sectID,course, CourseTitle,Tchrname, ROOM,
TotalStudents,ClassSize,CountFemale AS 'Females', CountMale AS 'Males',/**,**/
countBlack as 'Blacks', CountWhite as 'Whites',
CountOther As EthnicOthers,
CountEcoDis as ecoTotal, CountSPED1 As SPED, CountEngLang As LEP
from

(Select
count(Students.STUDENTID) as TotalStudents,
sum(CASE when gender='F' then 1 else 0 end) as countFemale,
sum(CASE when gender='M' then 1 else 0 end) as countMale,
sum(CASE WHEN Ethnic='3' then 1 else 0 end) as CountBlack,
sum(CASE WHEN Ethnic='5' then 1 else 0 end) as CountWhite,
sum(CASE WHEN Ethnic IN ('0','1','2','4','6') then 1 else 0 end) as CountOther,
sum(CASE WHEN Economic IN('F', 'R') then 1 else 0 end) as CountEcoDis,
sum(CASE WHEN ENGLANG IN ('2','3','A','B','C','D','E','F','G','H') then 1 else 0 end) as CountEngLang,

sum(CASE WHEN INSTRCTSETTING >' ' then 1 else 0 end) as CountSPED1,
Course,
left(crstitle,10)as 'CourseTitle', MAXCLASSSZ as 'ClassSize',beginperiod, endperiod, MstrClass.Tchrname,
sectionid as sectID,shortname as school, ROOM,term

FROM
StudentClass_HS StudentClass inner join MstrClass_HS MstrClass on (StudentClass.Schoolnum=MstrClass.schoolnum and StudentClass.classlink=MstrClass.classlink)
Inner join Students_HS Students on (StudentClass.schoolnum=Students.schoolnum and StudentClass.StudentID=Students.StudentID)
Inner join asch_allschools asch on MstrClass.schoolnum=asch.schoolnum
where (term in ('YR','S1'))
group by asch.shortname, Tchrname, beginperiod, endperiod, course,left(crstitle,10),sectionid, ROOM,MAXCLASSSZ,term)tmp
Group by school,course, CourseTitle,Tchrname, beginperiod, endperiod,ClassSize, CountFemale, CountMale,countBlack,
CountWhite,CountOther,
CountSPED1, sectID, CountEngLang,TotalStudents,CountEcoDis, ROOM,term


I'm wondering if it can be done in a query or should I make a temp table to hold all the classes that have more than one courseID.
Thanks for any ideas.

 

[icrf]

It shows up as two different lines because they're two different courses, and you're grouping by course. What's in CourseTitle when you combine Latin III and Latin IV? We had a recent thread183-1131282 where the poster used a UDF to concatenate a field in different rows together.

- Andrew
Text::Highlight - A language-neutral syntax highlighting module in Perl
also on SourceForge including demo