# Thread: Count and Sum query

1. Registered User
Join Date
Jun 2009
Posts
7

## Unanswered: Count and Sum query

Hello,

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.

Thanks

Code:
```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],
Sum(NumberOfParticipants) AS [Total # of Participants],

(SELECT SUM(NumberOfParticipants)
FROM ClassEvaluation
WHERE AgeofParticipants=1) AS [# of Children]
FROM ClassEvaluation

GROUP BY ClassName;```

2. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
I believe you want Sum() instead of Count().

3. Registered User
Join Date
Jun 2009
Posts
7
I want count for the first part (number of classes given for each category of participants-children, youth and adults) and then I want sum for the last part, the part that's in question-The total number of of participants that fall into each category (children, youth and adults).

4. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Look at it closer.

If you "count" each record with matching criteria as the number 1, how do you get the "sum" of all of your counts?

Don't let the verbiage get in the way of the logic...

5. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
change this --

Count(IIf(AgeOfParticipants=1,1,0)) AS [# for Children],
Count(IIf(AgeOfParticipants=2,1,0)) AS [# for Youth],

to this --

Count(IIf(AgeOfParticipants=1,'curly',NULL)) AS [# for Children],
Count(IIf(AgeOfParticipants=2,'larry',NULL)) AS [# for Youth],

6. Registered User
Join Date
Jun 2009
Posts
7
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.
Last edited by pwilly; 08-20-09 at 13:01.

7. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Code:
```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;```

8. Registered User
Join Date
Jun 2009
Posts
7
Perfect r937! That's exactly what I needed. Thanks!!

9. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102