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

    Unanswered: Count records in query

    I am writing a query that counts the number of seminars of a certain type that have been completed and counts the total number of seminar attendees for each type. I can do either count on its own just fine, but when I do them both at the same time the count of the number of seminars returns the same results as the count of the seminar attendees. Why?? This is what I have:


    SELECT DISTINCT SeminarType.SEM_TYPE, Count(SeminarAttendee.SemAttendID) AS CountOfSemAttendID, Count(Seminar.SEM_ID) AS CountOfSEM_ID
    FROM SeminarType RIGHT JOIN (Seminar INNER JOIN SeminarAttendee ON Seminar.SEM_ID = SeminarAttendee.SEM_ID) ON SeminarType.SeminarTypeID = Seminar.SeminarTypeID
    WHERE (((Seminar.Completed)=True))
    GROUP BY SeminarType.SEM_TYPE;
    Last edited by lskuff; 06-09-04 at 16:40.

  2. #2
    Join Date
    May 2004
    Posts
    90
    Sorry, I had to edit my first post. Look at it now.
    Last edited by lskuff; 06-09-04 at 16:53.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's because you have two unrelated relationships joined to the same single table

    you are getting "cross join" effects

    write a SELECT which includes the key columns, no GROUP BY, and using an ORDER BY for the keys

    you'll soon see what i mean
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    SELECT ST.SEM_TYPE, Count(S.SEM_ID) AS CountOfSEM_ID,
    (Select count(SA.SemAttendID)
    FROM SeminarAttendee SA where SA.SEM_ID IN
    (Select S.SEM_ID from Seminar S where S.Completed = TRUE))
    AS CountOfSemAttendID
    FROM SeminarType ST INNER JOIN Seminar S ON S.SEM_ID = ST.SEM_ID
    GROUP BY ST.SEM_TYPE;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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