MySQL: GROUP BY with HAVING returns empty set, but works without GROUP BY

Question

I have a table (cargo_route) that consists of following fields:

id,
truck_id,
lat,
lon,
radius

it's a shipping route where each row is each stop with lat,lon coordinates for a truck.

I need to calculate a distance between each stop and my (any input) point (lat lon) and bring results if the distance is less than specified radius (for that stop). (Basically search whether the city is on the route or too far away from any stop on the route)

HERE is my query (which works fine)

SELECT
`truck_id`,
`radius`,
(
    3959 * acos(
        cos(radians(39.9611755)) * cos(radians(cargo_route.lat)) * cos(
            radians(cargo_route.lon) - radians(- 82.9987942)
        ) + sin(radians(39.9611755)) * sin(radians(cargo_route.lat))
    )
) AS distance FROM
(`cargo_route`) HAVING `distance` < `radius`

The code works.... BUT, it brings me several rows with the same truck_id, (basically every single stop that's close enough to the searched point)

When I try to "GROUP BY truck_id" to only get ids of trucks that pass through my point, I get an empty set :(

I could resolve this within PHP logic but I'd rather get SQL to return me what I really need than iterate through an array.

I did research and learned that GROUP BY executes first and HAVING ends up "not having" distance < radius because group by grabs the first row (hence HAVING producing the empty set). I also tried ORDER B but it has no effect.

HEre is the query that returns EMPTY SET

    SELECT
`truck_id`,
`radius`,
(
    3959 * acos(
        cos(radians(39.9611755)) * cos(radians(cargo_route.lat)) * cos(
            radians(cargo_route.lon) - radians(- 82.9987942)
        ) + sin(radians(39.9611755)) * sin(radians(cargo_route.lat))
    )
) AS distance FROM 
(`cargo_route`)  GROUP BY truck_id HAVING `distance` < `radius`

QUESTION: IS there way to execute HAVING and then group by after? Perhaps a slightly different syntax?

I tried reordering HAVING and GROUP BY but it throws me an error. ALso, I cannot use WHERE because 'distance' is an aggregated column.

************EDIT _ SOLVED *******************

Canno answer my own question:

Guys, actually found an answer here: SELECT From MySQL View With HAVING Clause Returns Empty Result Set

Basically, put SELECT * FROM (my query) S WHERE distance < radius GROUP BY truck_id

You must use all your selected fields in your group by statement. I think Your query returns error, isn't it ? — Vahe Shadunts, Mar 05 '13 at 00:48
Unfortunately MySQL by default permits a query to select fields which are not included in the GROUP BY clause, thus the confusion here. On other SQL databases, this query would indeed return an error. — Kurt Raschke, Mar 05 '13 at 00:51
really? sorry guys I'm working on PostgreSQL, because mysql have many bugs. I think it can be interesting http://www.youtube.com/watch?v=1PoFIohBSM4 — Vahe Shadunts, Mar 05 '13 at 00:54
@TrueStory did you really ask a question and then found the answer in 10 minutes on you own? Your can use the search function before you ask a question. There is no need to register or post and question to use the search function. — Mr. Radical, Mar 05 '13 at 00:57

Kamil · Answer 1 · 2013-03-05T01:15:42.090

QUESTION: IS there way to execute HAVING and then group by after?

HAVING is for filtering output data. WHERE operates on input data.

You have to use some subquery, maybe like this.

SELECT `truck_id`, `radius`, `distance`
FROM
(
    SELECT
    `truck_id`,
    `radius`,
    (
        3959 * acos(
            cos(radians(39.9611755)) * cos(radians(cargo_route.lat)) * cos(
                radians(cargo_route.lon) - radians(- 82.9987942)
            ) + sin(radians(39.9611755)) * sin(radians(cargo_route.lat))
        )
    ) AS distance FROM 
    (`cargo_route`) GROUP BY truck_id;
)

HAVING `distance` < `radius`;

Barranka · Answer 2 · 2013-03-05T01:05:16.040

1

Remember that having is meant to work with aggregate data (that is, if you want to use having then you need to define a grouping criteria).

A quick guide I keep is this:

select [the data you want]
from [the tables you have]
where [the filters you need to apply ON YOUR "RAW" DATA]
group by [your grouping criteria]
having [the filters you need to apply TO THE GROUPED DATA]

edited Mar 05 '13 at 01:05

answered Mar 05 '13 at 00:49

Barranka

20,547
13
65
83

score 1 · Answer 3 · answered Mar 05 '13 at 00:56

MySQL is leading you astray. The syntax that works for you is actually illegal.

Based on your stated intent, the haing clause should be a where.

SELECT
`truck_id`,
`radius`,
(
    3959 * acos(
        cos(radians(39.9611755)) * cos(radians(cargo_route.lat)) * cos(
            radians(cargo_route.lon) - radians(- 82.9987942)
        ) + sin(radians(39.9611755)) * sin(radians(cargo_route.lat))
    )
) AS distance FROM
(`cargo_route`) where (
    3959 * acos(
        cos(radians(39.9611755)) * cos(radians(cargo_route.lat)) * cos(
            radians(cargo_route.lon) - radians(- 82.9987942)
        ) + sin(radians(39.9611755)) * sin(radians(cargo_route.lat))
    )
)` < `radius`

Jan.J · Answer 4 · 2013-03-05T00:53:03.917

0

It might work if you move distance evaluation directly to WHERE clause. You can also wrap everything(without GROUP BY) into subquery and in GROUP BY in parent query.

edited Mar 05 '13 at 00:53

answered Mar 05 '13 at 00:46

Jan.J

3,050
1
23
33

MySQL: GROUP BY with HAVING returns empty set, but works without GROUP BY

4 Answers4