×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

GROUP BY PROBLEM
2

GROUP BY PROBLEM

GROUP BY PROBLEM

(OP)
Hey Guys,
Great looking forum, hope you can help me with this. I am trying to do a GROUP BY, but not having much luck.

SELECT `tblAttraction`.`AttractionName`, max(`tblAttraction`.`AttractionCapacity`), `tblAttraction`.`AttractionMinAge`, `tblThemePark`.`ParkName`
FROM `tblThemePark`
 LEFT JOIN `tblAttraction` ON `tblThemePark`.`ParkID` = `tblAttraction`.`ParkID`
WHERE (`tblAttraction`.`AttractionName` <11)
GROUP BY `tblThemePark`.`ParkName`

Basically, I want the GROUP BY to say which is the most popular ride at each theme park. I thought this was the way, any idea where i'm going wrong?

Cheers

RE: GROUP BY PROBLEM

here's your query, reworked slightly:

CODE

SELECT a.AttractionName
     , a.AttractionCapacity
     , a.AttractionMinAge
     , p.ParkName
  FROM tblThemePark AS p
INNER
  JOIN ( SELECT ParkID
              , MAX(AttractionCapacity) AS max_cap
           FROM tblAttraction
          WHERE AttractionName < 11
         GROUP
             BY ParkID ) AS m
    ON m.ParkID = p.ParkID
INNER
  JOIN tblAttraction AS a
    ON a.ParkID = p.ParkID
   AND a.AttractionCapacity = m.max_cap
   AND a.AttractionName < 11
GROUP
    BY p.ParkName
note that "max capacity" is probably not an indicator of popularity

smile

r937.com | rudy.ca

RE: GROUP BY PROBLEM

I'd solve it like this:

SELECT A.AttractionName,
       A.AttractionCapacity,
       A.AttractionMinAge,
       TP.ParkName
FROM tblThemePark as TP
 JOIN tblAttraction as A ON TP.ParkID = A.ParkID
WHERE A.AttractionName < 11
  AND A.AttractionCapacity = (SELECT MAX(AttractionCapacity)
                              FROM tblAttraction
                              WHERE ParkID = TP.ParkID)


Will list all top attractions, even if there's a tie.

Core SQL-99 compliant.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close