Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2012
    Posts
    1

    Unanswered: Finding Max of two nested aggregate functions in SQL

    I have an MS Access 2007 Database with the following tables and attributes:

    • Characters: Name
    • Planets: Name
    • TimeTable: Planet's Name, Character's Name, Movie

    Time Table denotes if a character has visited a planet, there may be multiple entries for one planet.
    The query I'm trying to get is this one:
    For each movie, which characters visited the highest number of planets?

    This is my attempt:

    SELECT T.Movie, T.[Character's Name], Count(T.[Planet's Name]) AS planets
    FROM TimeTable T
    GROUP BY T.Movie, T.[Character's Name]
    HAVING Count(T.[Planet's Name]) >= ALL (SELECT Count(T2.[Planet's Name])
    FROM TimeTable T2 WHERE T.Movie = T2.Movie);
    It gives me an empty result though. What is wrong with my query?
    Last edited by SummertimeClothes; 10-30-12 at 07:34.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    As a first guess, you're using an aggregate function in your subquery without grouping on anything. To me, that suggests that your comparison value for the count of planet names as grouped on character name and movie will be the total number of records in the table, and so you're never going to get a match (unless all movies took place on the same planet).

    Also, before you get too far into this design, please rename your fields. Access field names should only consist of alphanumeric characters and underscores. No spaces or punctuation! There are naming conventions that you can adopt to deal with the requirement for multiple word headings such as mixed case or camel case, and they will allow you to write SQL statements without needing to signify field names with brackets.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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