10-30-12, 07:25 #1Registered User
- Join Date
- Oct 2012
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);
Last edited by SummertimeClothes; 10-30-12 at 07:34.
10-30-12, 10:42 #2Registered User
Provided Answers: 2
- Join Date
- Sep 2006
- Surrey, UK
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.