By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,157 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,157 IT Pros & Developers. It's quick & easy.

I Can't select the fields i need in the query result!

P: 1
I have this query and it works fine however i need the query to display the forename and surname and currently it only displays the max value of the sum it performs

Does anybody know what i can do?

Expand|Select|Wrap|Line Numbers
  1. SELECT MAX(Intermediate.Total_Amount_Of_Likes) AS Total_Amount_Of_Likes
  2. FROM (
  3. SELECT Forename, Surname, Sum(Likes) AS Total_Amount_Of_Likes
  4. FROM Instructors, Exercise_Class_Type, Exercise_Class_Staff, Exercise_Class_Booking, Feedback
  5. WHERE Exercise_Class_Type.Exercise_Class_ID = Exercise_Class_Staff.Exercise_Class_ID
  6. AND Exercise_Class_Type.Exercise_Class_ID = Exercise_Class_Booking.Exercise_Class_ID 
  7. AND Exercise_Class_Booking.Exercise_Booking_ID = Feedback.Exercise_Booking_ID
  8. AND Instructors.Instructor_ID = Exercise_Class_Staff.Instructor_ID
  9. AND Start_Date_Time >= Date() - 30
  10. GROUP BY Forename, Surname
  11. ) Intermediate;
Apr 29 '12 #1
Share this Question
Share on Google+
2 Replies

Seth Schrock
Expert 2.5K+
P: 2,941
What you have here is a sub-query running in the background. The only field that will be displayed is the max value as you have discovered. You should be able to get the results that you are looking for by adding the following to the end of line 1 of your code:

Expand|Select|Wrap|Line Numbers
  1. , Forename, Surname
This will pull those fields from the sub-query (the subquery is the part between the ( in line 2 and the ) in line 11)
Apr 30 '12 #2

Expert Mod 15k+
P: 31,494
Sorry Seth, but the outer query is also aggregated (IE. as well as the subquery). The OP is after identifying which of the aggregated records of the subquery is reflected by the value returned as the maximum value for [Total_Amount_Of_Likes].
Apr 30 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.