How to use GROUP By and HAVING in MySQL

Question

The first two queries work fine, the 3rd one runs but brings back nothing when there should be results. How can I get the 3rd one to bring back results. It seems to me the GROUP BY and HAVING are not working together.

The 2nd query returns 32 Active Status and 7 Pending Status, so the 3rd query should return a summary of the 2nd query, but it is not.

SELECT COUNT(DISTINCT MLSNumber) AS TOTAL, `Status`
FROM Residential 
WHERE PropertyType='Single Family' AND Status IN ("Active", "Pending")  
GROUP BY `Status`;

SELECT MLSNumber, `Status`, 
( 3959 * acos( cos( radians(21.380936) ) * cos( radians( Latitude ) ) 
 * cos( radians( Longitude ) - radians(-157.757438) ) + sin( radians(21.380936) ) 
 * sin(radians(Latitude)) ) ) AS distance 
FROM Residential 
WHERE PropertyType='Single Family' AND Status IN ("Active", "Pending")  
HAVING distance < 2;

SELECT COUNT(DISTINCT MLSNumber) AS TOTAL, `Status`, 
( 3959 * acos( cos( radians(21.380936) ) * cos( radians( Latitude ) ) 
 * cos( radians( Longitude ) - radians(-157.757438) ) + sin( radians(21.380936) ) 
 * sin(radians(Latitude)) ) ) AS distance 
FROM Residential 
WHERE PropertyType='Single Family' AND Status IN ("Active", "Pending")  
GROUP BY `Status`
HAVING distance < 2;

The second one should be using `WHERE`. `HAVING` is only used for aggregation. — AdamMc331, Jul 02 '15 at 17:20

wcm · Accepted Answer · 2015-07-02T17:30:46.280

When you are using GROUP BY, you need to be using aggregate functions for all fields not included in your GROUP BY clause.

What I think you want is to have the calculated distance be part of your where clause and get rid of the HAVING clause.

WHERE PropertyType='Single Family' AND Status IN ("Active", "Pending")  
    AND ( 3959 * acos( cos( radians(21.380936) ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians(-157.757438) ) + sin( radians(21.380936) ) * sin(radians(Latitude)) ) ) < 2

score 0 · Answer 2 · answered Jul 02 '15 at 17:22

You cannot use HAVING in this example, as that clause is used for specifying conditions on aggregate columns.

In your example, distance is not an aggregated column, but one that is calculated for each row, so if you want to look for rows with distance less than two you should use a WHERE clause. However, I'm not sure you want to be grouping in this case, as distance looks like it applies to individual rows, not something for the group.

The only column in these examples that would belong in HAVING is your count functions.

Ray · Answer 3 · 2015-07-02T17:28:29.637

I'm assuming the goal of #3 is to find the total count of MLS entries within 2 miles.

The problem you're having is that by grouping by Status in the final query, you're applying that first, before doing the calculation on latitude and longitude. Therefore, only one record's latitude and longitude get's calculated for each Status grouped group.

Try wrapping Query #2 inside another select that groups by status:

  SELECT COUNT(DISTINCT i.MLSNumber) AS TOTAL, i.Status FROM 
    ( SELECT MLSNumber, `Status`, 
         ( 3959 * acos( cos( radians(21.380936) ) * cos( radians( Latitude ) ) *    cos( radians( Longitude ) - radians(-157.757438) ) + sin( radians(21.380936) ) * sin(radians(Latitude)) ) ) AS distance 
     FROM Residential 
     WHERE PropertyType='Single Family' AND Status IN ("Active", "Pending")  
     HAVING distance < 2) as i  Group by Status

You may need to tweak the query a bit, but that's the gist--I don't have your schema to experiment.

Also, as noted by a comment to your earlier post, you can ditch the HAVING and just use a where since you're not grouping.

How to use GROUP By and HAVING in MySQL

3 Answers3