Results 1 to 9 of 9
  1. #1
    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.

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

    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], 
    Count(IIf(AgeOfParticipants=3,1,0)) AS [# for Adults], 
    Sum(NumberOfParticipants) AS [Total # of Participants], 
    
    (SELECT SUM(NumberOfParticipants) 
    FROM ClassEvaluation 
    WHERE AgeofParticipants=1) AS [# of Children]
    FROM ClassEvaluation
    
    GROUP BY ClassName;

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I believe you want Sum() instead of Count().
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    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. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change this --

    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],

    to this --

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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    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. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Wish I would have thought of that.

    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •