How to correctly write a condition for LEFT JOIN using COUNT for COUNT?

Question

MySQL

Dear gurus, lack of knowledge in SQL? help me please. I'm using Mysql 5. The query ideally looks like this:

SELECT r.*, r.`seats`- COUNT(re.`id`) as seatsFree FROM `tours` as r
    LEFT JOIN `requests` as re ON re.`tour_id`=r.`id` and re.`visible`=1 and re.`status`=3 
    WHERE COUNT(re.`id`)>5
    group by  r.`id`

And naturally dies on the line:
WHERE COUNT(re.`id`)>5
Writes Invalid use of group function
I think that you need to write HAVING but how? How to make a request?

Answer 1

decided like this:

SELECT r.*, r.`seats`- COUNT(re.`id`) as seatsFree FROM `tours` as r
    LEFT JOIN `requests` as re ON re.`tour_id`=r.`id` and re.`visible`=1 and re.`status`=3 
    WHERE 1 
    group by  r.`id`
    HAVING  (seatsFree <=90) 
    ORDER BY r.`finished` DESC
    LIMIT 0,2

But now it is not clear how to calculate the number of samples
. Something like

SELECT COUNT(r.id) as num, r.`seats`- COUNT(re.`id`) as seatsFree FROM `tours` as r
    LEFT JOIN `requests` as re ON re.`tour_id`=r.`id` and re.`visible`=1 and re.`status`=3  WHERE 1  group by  r.`id` HAVING  (seatsFree <=90)  order by num desc
    LIMIT 1

But it works sloppy.

Answer 2

I haven't looked at the gist, but maybe something like this:

SELECT r.*, r.`seats`- COUNT(re.`id`) as seatsFree FROM `tours` as r
    LEFT JOIN `requests` as re ON re.`tour_id`=r.`id` and re.`visible`=1 and re.`status`=3 
    group by  r.`id`
    having COUNT(re.`id`)>5