Question 63636 in PhpMyAdmin Question posted in
The official documentation can be found here.

Mysql query with multiple having conditions creating error – Phpmyadmin

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
Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    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!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search