Mysql query with multiple having conditions creating error-CodePudding

I am trying multiple having but problem is with multiple having conditions any one guide me how to adjust two having in same query

Here is SQL Query :

SELECT    schools.*,
          schools.required_gpa                                                                                                                                                                                           AS gpa,
          (college_act_scores.min_act   college_act_scores.max_act)/2                                                                                                                                                    AS act_avrg ,
          (college_sat_scores.min_sat   college_sat_scores.max_sat)/2                                                                                                                                                    AS sat_avrg,
          (6371                       * Acos( Cos( Radians(31.4699398) ) * Cos( Radians( latitude ) ) * Cos( Radians( longtitude ) - Radians(74.3096108) )   Sin( Radians(31.4699398) ) * Sin( Radians( latitude ) ) ) ) AS distance
FROM      schools
LEFT JOIN college_paying1
ON        college_paying1.college_id = schools.id
LEFT JOIN college_act_scores
ON        (
                    college_act_scores.college_id = schools.id
          AND       college_act_scores.college_child_sub_cat_id = 144)
LEFT JOIN college_sat_scores
ON        (
                    college_sat_scores.college_id = schools.id
          AND       college_sat_scores.college_child_sub_cat_id = 136)
WHERE     college_paying1.on_campus >= 0
AND       college_paying1.on_campus <=80348
AND       college_paying1.college_child_sub_cat_id =120
HAVING    (
                    act_avrg BETWEEN 0 AND       36)
having    (
                    sat_avrg BETWEEN 0 AND       1600)
GROUP BY  schools.id
ORDER BY  distance ASC limit 0, 10

CodePudding user response：

You need to move the HAVING clause after GROUP BY. Document here

This should work:

select schools.*,schools.required_gpa as gpa,
(college_act_scores.min_act   college_act_scores.max_act)/2  as act_avrg , 
(college_sat_scores.min_sat   college_sat_scores.max_sat)/2 as sat_avrg, 
(6371 * acos( cos( radians(31.4699398) ) * cos( radians( latitude ) ) * cos( radians( longtitude ) - radians(74.3096108) )   sin( radians(31.4699398) ) * sin( radians( latitude ) ) ) ) AS distance 

from schools 
left join college_paying1 on college_paying1.college_id = schools.id 
left join college_act_scores on (college_act_scores.college_id = schools.id 
    AND college_act_scores.college_child_sub_cat_id = 144) 
left join college_sat_scores on (college_sat_scores.college_id = schools.id 
    AND college_sat_scores.college_child_sub_cat_id = 136)  

where college_paying1.on_campus >= 0 
and college_paying1.on_campus <=80348 
and  college_paying1.college_child_sub_Cat_id =120  

GROUP BY schools.id 
HAVING ( act_avrg BETWEEN  0 AND 36) AND ( sat_avrg BETWEEN  0 AND 1600)  
order by distance asc 
LIMIT 0 , 10

Hope this helps! Cheers!

CodePudding user response：

Instead of

HAVING ( act_avrg BETWEEN  0 AND 36)  HAVING ( sat_avrg BETWEEN  0 AND 1600)

Put

HAVING (act_avrg BETWEEN  0 AND 36) AND (sat_avrg BETWEEN  0 AND 1600)