Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2008
    Posts
    34

    Unanswered: complicated group by ranges

    I have the following statement that shows me all records having a calculated field that is > 100.

    SELECT amember_updates.member_id, amember_updates.update_id, SQRT( POW( ( 69.1 * ( amember_coops.lattitude - amember_updates.lattitude ) ) , 2 ) + POW( ( 53 * ( amember_coops.longitude - amember_updates.longitude ) ) , 2 ) ) AS distance
    FROM amember_coops,amember_updates where amember_coops.coop_id = amember_updates.coop_id
    HAVING distance >= 100
    ORDER BY distance ASC

    It takes the latitude and longitude of a record in the coops table and calculates the distance using the latitude and longitude in the updates table.

    If works fine, but id like to show the results grouped by ranges of distances like 0-50, 51-100,101-150, etc.

    Ive seen group by ranges done with CASE, but the examples never use more than one table or a calculated field.

    How do I do this?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How about something like this?

    [untested]
    Code:
    SELECT member_id
         , update_id
         , distance
         , Floor(distance / 50) As lower_boundary
         , Ceiling(distance / 50) As upper_boundary
    FROM   (
            SELECT amember_updates.member_id
                 , amember_updates.update_id
                 , SQRT(POW((69.1 * (amember_coops.lattitude - amember_updates.lattitude)), 2)
                   +    POW((53.0 * (amember_coops.longitude - amember_updates.longitude)), 2)) As distance 
            FROM   amember_coops
             INNER
              JOIN amember_updates
                ON amember_updates.coop_id = amember_coops.coop_id
           ) As x
    WHERE  distance >= 100
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2008
    Posts
    34

    nope

    That code just showed the results ungrouped

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider a case statement to generate a range number and group on that
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2008
    Posts
    34
    Tht's what I'm asking how to do, a CASE statmenet with a calculated value for the case field.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can't show grouping and detail at the same time in a single SQL statement (without resorting to tricks), so either member_id or grouping is possible but not both.

    If you want member_id, then you can't have grouping.

    If you want grouping, GROUP BY one or both of the two expressions that gvee supplied in his example.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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