# Thread: complicated group by ranges

1. Registered User
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
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. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445

[untested]
Code:
```SELECT member_id
, update_id
, distance
, Floor(distance / 50) As lower_boundary
, Ceiling(distance / 50) As upper_boundary
FROM   (
, 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
) As x
WHERE  distance >= 100```

3. Registered User
Join Date
Sep 2008
Posts
34

## nope

That code just showed the results ungrouped

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
consider a case statement to generate a range number and group on that

5. Registered User
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

#### Posting Permissions

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