I have a database that has id and distance columns. I need to count the number of ids that are between a certain distance.
For example, I want to count how many ids are between 1km and 2km in distance, so I'm using this code:
SELECT COUNT (distance)
FROM table
WHERE distance BETWEEN 1000 AND 2000
-- Returns a COUNT of 240,600
When I want to count the number of ids that are between 2km and 3km and I use the same query, but the "2km" value is counted in both queries as the BETWEEN operator is inclusive.
SELECT COUNT (distance)
FROM table
WHERE distance BETWEEN 2000 AND 3000
-- Returns a COUNT of 353,440
As I understand it the two queries above will both include rows where the distance is exactly 2000.
I'm curious to know if there is another way to count things like distance (and there are a lot of rows) or do I need to GROUP BY And then count?
According to SQL specs, this expression:
WHERE distance BETWEEN 1000 AND 2000
is same as:
WHERE distance >= 1000 AND distance <= 2000
From what I understand, you need to remove = from one of the endpoints to create mutually exclusive ranges. Depending on your definition of between 1km and 2km one of these set of conditions should be used:
WHERE distance >= 0 AND distance < 1000
WHERE distance >= 1000 AND distance < 2000
WHERE distance >= 2000 AND distance < 3000
Or
WHERE distance <= 1000
WHERE distance > 1000 AND distance <= 2000
WHERE distance > 2000 AND distance <= 3000