Mysql query with multiple heaving conditions creating error-CodePudding

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

Problem is with multiple heaving conditions any one guide me how to adjust two heaving in same query

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)

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!