Having (count) combined with rest of query hangup

[RyanSF07]

Hi,

 

This query works without any trouble:

 

SELECT video_id
FROM `quiz`
GROUP BY video_id
HAVING count( question ) <4
ORDER BY video_id ASC
LIMIT 0 , 3000

 

And I'd like to work the "Having Count ()" part into this query below. Note -- without this part: HAVING count (quiz.question) >4 :: this query works perfectly. 

 

SELECT DISTINCT video.id, title, description_text, category_text, level_text, pass_text, user_name, quiz.video_id, quiz.question,
DATE_FORMAT(date, '%M %D, %Y') as date 
FROM video, registered_users, quiz 
WHERE video.user_id = registered_users.id 
AND video.category_text = 'English' 
AND video.pass_text = 'new_quiz' 
AND quiz.video_id = video.id 
HAVING count( quiz.question ) >4 
ORDER BY id DESC

 

Can you help me with the syntax needed to smoothly incorporate "HAVING count (quiz.question) >4" into the above query?

 

I know I have a long way to go to understand how to best optimizing queries -- but I'm getting there.  Thank you very much for your help! 

Ryan

[kickstart]

Hi

 

The Having goes with a group by clause. I think you query would try bringing back all rows  where the row is counted more than 4 times (so no records).

 

SELECT video.id, title, description_text, category_text, level_text, pass_text, user_name, quiz.video_id, DATE_FORMAT(date, '%M %D, %Y') as formatdate, count( quiz.question ) AS QuestionCount
FROM video, registered_users, quiz
WHERE video.user_id = registered_users.id
AND video.category_text = 'English'
AND video.pass_text = 'new_quiz'
AND quiz.video_id = video.id
GROUP BY video.id, title, description_text, category_text, level_text, pass_text, user_name, quiz.video_id,  formatdate
HAVING QuestionCount >4
ORDER BY id DESC

 

All the best

 

Keith