Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    90

    Unanswered: Counting in query

    I am having problems getting a query I am writing to work correctly. My database holds the records of seminars given. Each seminar has a Seminar Type: type1, type2, type3, etc... there are a limited number of seminar types and many seminars have the same types.
    My query is looking at the dates seminars were given and putting them in a fiscal year (2001, 2002, etc..) and grouped by that. Secondly they are being grouped by the seminar type. This part is fine. But I am running into problems because I need to count the number of that type of seminars given in a fiscal period and count the number of attendees at that seminar type for that fiscal period. So what it SHOULD look like is this:

    Fiscal Year: Seminar Type: # of Attendee's: # of Seminars:
    2003 Type1 36 5
    2003 Type3 23 4
    2003 Type4 76 8
    -------
    2004 Type1 10 1
    2004 Type2 123 13
    2004 Type3 23 3
    etc...


    I am running into problems counting the number of attendees and number of seminars. In my query I can count either by them self just fine. But when I count them together in the query number of seminars is wrong. It returns the same value as the number of attendees.
    So I have tried to count the number of seminars in a seperate query and link it to this one. But if I link on SeminarType then if there is a type1 seminar in one fiscal period and another type1 in another period the value will be the same for both of them.

    Anyone know how I can either count both the values correctly in 1 query or write another query that will correctly join with my main query?

    This is the code I have for counting both in the same query which does not work:


    SELECT Year(DateAdd("m",6,[Seminar].[Date])) AS FiscalYear, SeminarType.SEM_TYPE, Count(Seminar.SEM_ID) AS CountOfSEM_ID, Count(SeminarAttendee.SemAttendID) AS CountOfSemAttendID
    FROM SeminarType INNER JOIN (Seminar INNER JOIN SeminarAttendee ON Seminar.SEM_ID = SeminarAttendee.SEM_ID) ON SeminarType.SeminarTypeID = Seminar.SeminarTypeID
    GROUP BY Year(DateAdd("m",6,[Seminar].[Date])), SeminarType.SEM_TYPE, Seminar.Completed
    HAVING (((Seminar.Completed)=True));
    Last edited by lskuff; 06-11-04 at 18:58.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's one way to solve this (out of many):

    save this query as Seminar_Q --
    Code:
    select year(dateadd("m",6,[Seminar].[Date])) as FiscalYear
         , SeminarType.SEM_TYPE
         , count(Seminar.SEM_ID) as countOfSEM_ID
      from SeminarType 
    inner 
      join Seminar 
        on SeminarType.SeminarTypeID 
         = Seminar.SeminarTypeID
     where Seminar.Completed = True
    group 
         by year(dateadd("m",6,[Seminar].[Date]))
          , SeminarType.SEM_TYPE
    then save this query as Attendee_Q --
    Code:
    select year(dateadd("m",6,[Seminar].[Date])) as FiscalYear
         , SeminarType.SEM_TYPE
         , count(SeminarAttendee.SemAttendID) as countOfSemAttendID
      from (
           SeminarType 
    inner 
      join Seminar 
        on SeminarType.SeminarTypeID 
         = Seminar.SeminarTypeID
           )
    inner 
      join SeminarAttendee 
        on Seminar.SEM_ID 
         = SeminarAttendee.SEM_ID
     where Seminar.Completed = True
    group 
         by year(dateadd("m",6,[Seminar].[Date]))
          , SeminarType.SEM_TYPE
    now your main query is --
    Code:
    select S.FiscalYear
         , S.SEM_TYPE
         , S.countOfSEM_ID
         , A.countOfSemAttendID
      from Seminar_Q S 
    inner 
      join Attendee_Q 
        on S.FiscalYear = A.FiscalYear
       and S.SEM_TYPE   = A.SEM_TYPE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2004
    Posts
    90
    Sweet, thanks!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're welcome

    typo in last query, Attendee_Q needs to have table alias A
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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