Count and Sum query

[pwilly]

I am seeking help with creating a query that will return the total number of classes, total number of classes broken down by age group, total number of participants for each class and total number of participants for each class broken down by age group. I seem to have everything except the total number of participants broken down by age group. I have it to where it returns the total number of children but it doesn't list the correct number of children for each class, it just lists the correct number of children for the first class and then repeats the same number for the rest of the classes.

I'm sure i'm going about the number of participants part all wrong. Please help me if you can.

Thanks

[LEFT]SELECT ClassName, 
Count(ClassName) AS [Total # of Classes], 
Count(IIf(AgeOfParticipants=1,1,0)) AS [# for Children], 
Count(IIf(AgeOfParticipants=2,1,0)) AS [# for Youth], 
Count(IIf(AgeOfParticipants=3,1,0)) AS [# for Adults], 
Sum(NumberOfParticipants) AS [Total # of Participants], [/LEFT]
 
[LEFT](SELECT SUM(NumberOfParticipants) 
FROM ClassEvaluation 
WHERE AgeofParticipants=1) AS [# of Children]
FROM ClassEvaluation[/LEFT]
 
[LEFT]GROUP BY ClassName;[/LEFT]

 

[pbaldy]

Try this

Sum(IIf(AgeOfParticipants=1,1,0))

 

[pwilly]

The problem is that I need to return the sum of a field called NumberOfParticipants which is a number field. So for instance this field may contain 12 children in class A that took place 2 weeks ago in one record, 6 children in class B a week ago in the next record and 23 youth in class A a week ago in another record. I need this portion of the query to be able to return the fact that 35 children total have attended Class A and 6 children total have attended class B. I will need to run this report every 6 months.

 

[pbaldy]

Have you played with a totals query? It sounds like you want to group on class and sum this NumberOfParticipants field.

 

[pwilly]

I actually just got what I needed from another forum. Thanks to pbaldy for responding! Here is the resolution in case anyone's interested:

[LEFT]SELECT ClassName
    , COUNT(*) AS [Total # of Classes]
    , SUM(IIf(AgeOfParticipants=1,NumberOfParticipants,NULL)) AS [# for Children]
    , SUM(IIf(AgeOfParticipants=2,NumberOfParticipants,NULL)) AS [# for Youth]
    , SUM(IIf(AgeOfParticipants=3,NumberOfParticipants,NULL)) AS [# for Adults]
    , SUM(NumberOfParticipants) AS [Total # of Participants]
 FROM ClassEvaluation
GROUP 
   BY ClassName;[/LEFT]