Case question- too many different case stmts/group by? 1

[jmj]

I need to run some counts on demographic data- it needs to all be in one report. But I'm having some problems figuring it out.
I am using a case staement to do a count inside a select statement. (See below for code).
Counts correctly when there's only a few things to count, however, the number changes when I add new fields. For example if I'm counting up the gender (5male/6 female) and the scholarshiptype-for the whole group (3Full/1partial)- it's fine. But if I add more fields like EC ethnic background suddenly it'll change my totals in the other fields. I have a feeling it has to do with the group clause... any ideas?
thanks
Here's my code

Select school, Course,tchname, begperiod, endperiod,
sum(CountFemale) AS 'Females', sum(CountMale) AS 'Males',/**,**/
sum(countBlack) as 'Blacks', sum(CountWhite) as 'Whites', sum(CountHispanic)as 'Hispanics',
sum(CountOther + CountAmIndian + CountAsian + CountHispanic + CountHawaiian) As EthnicOthers,
sum(scholTyps1+scholTyp2) As Scholarships
from
/**Start of case select statement**/ (Select
/**Count the numbes of m/f**/
CASE when gender='F' then count(*) else 0 end as countFemale,
CASE when gender='M' then count(*) else 0 end as countMale,

/**count the ethnicity**/
CASE WHEN EC='1' then count(*) else 0 end as CountAmIndian,
CASE WHEN EC='2' then count(*) else 0 end as CountAsian,
CASE WHEN EC='3' then count(*) else 0 end as CountBlack,
CASE WHEN EC='4' then count(*) else 0 end as CountHawaiian,

CASE WHEN EC='5' then count(*) else 0 end as CountHispanic,
CASE WHEN EC='6' then count(*) else 0 end as CountWhite,
CASE WHEN EC='7' then count(*) else 0 end as CountOther,

/**Count the total number of scholarships given to group**/
CASE WHEN ScholType = ('F') then count(*) else 0 end as scholTyp1,
CASE WHEN ScholType = ('P') then count(*) else 0 end as scholTyp2,
ms.school,left(crstitle,10)as 'Course', begperiod, endperiod, ms.tchname
FROM
tbl_ClassList cl inner join tbl_MasterSched ms on (cl.school=ms.school and cl.classlink=ms.classlink)
Inner join tbl_StudentData sd on (cl.school=sd.school and cl.studentID=sd.studentID)
where duration in ('YR','S1')
group by ms.school, tchname, begperiod, endperiod, left(crstitle,10), EC,gender, /****/Scholype
having count(tchname + begperiod +endperiod) >=2 )tmp
Group by school, Course,tchname, begperiod, endperiod
Order by begPeriod

 

[r937]

start with this and see if it helps --

select school
     , Course
     , tchname
     , begperiod
     , endperiod
     , CountFemale     AS Females
     , CountMale       AS Males
     , countBlack      as Blacks
     , CountWhite      as Whites
     , CountHispanic   as Hispanics
     , CountOther 
      +CountAmIndian 
      +CountAsian 
      +CountHispanic 
      +CountHawaiian   As EthnicOthers
     , scholTyp1
      +scholTyp2       As Scholarships
  from (
       select 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 EC='1' 
                       then 1 else 0 
                   end) as CountAmIndian
            , sum(case when EC='2' 
                       then 1 else 0 
                   end) as CountAsian
            , sum(case when EC='3' 
                       then 1 else 0 
                   end) as CountBlack
            , sum(case when EC='4' 
                       then 1 else 0 
                   end) as CountHawaiian
            , sum(case when EC='5' 
                       then 1 else 0 
                   end) as CountHispanic
            , sum(case when EC='6' 
                       then 1 else 0 
                   end) as CountWhite
            , sum(case when EC='7' 
                       then 1 else 0 
                   end) as CountOther
            , sum(case when ScholType = 'F' 
                       then 1 else 0 
                   end) as scholTyp1
            , sum(case when ScholType = 'P' 
                       then 1 else 0 
                   end) as scholTyp2
            , ms.school
            , left(crstitle,10) as Course
            , begperiod
            , endperiod
            , ms.tchname
         from tbl_ClassList cl 
       inner 
         join tbl_MasterSched ms 
           on cl.school=ms.school 
          and cl.classlink=ms.classlink
       inner 
         join tbl_StudentData sd 
           on cl.school=sd.school 
          and cl.studentID=sd.studentID
        where duration in ('YR','S1')
       group 
           by ms.school
            , left(crstitle,10)
            , begperiod
            , endperiod
            , ms.tchname
 /* WASN'T SURE IF NEXT LINE WAS A TYPO ...
            , EC,gender,  
              ... SO I COMMENTED IT OUT */ 
       ) as tmp
order 
    by begPeriod

r937.com | rudy.ca

 

[jmj]

Thanks! That really worked well. And it's much easier to read then the way I was going about doing it!