[RESOLVED] Help with mysql query and using group and count

[timski72]

Hello,

the following query

select , count(videowords.wordid) from videowords group by videowords.wordid having count() < 2

VIDEOS_VIDEOID SEQUENCE WORDID count(videowords.wordid)
4 | 4 | 8 | 1
5 | 4 | 9 | 1
142 | 3 | 456 | 1
142 | 4 | 457 | 1

So far so good. It's listing only the records where the wordid appears less than twice.

I would like to narrow the selection down further to exclude those records where VIDEOS_VIDEOID = 142.

I tried the following:

select count(videowords.wordid) from videowords group by videowords.wordid having count() < 2 AND videowords.videos_videoid <> '142'

but I get the following error:

#1054 - Unknown column 'videowords.videos_videoid' in 'having clause'

Any ideas how I can achieve this?

Thanks,
Tim.

[bradgrafelman]

timski72 wrote:

I would like to narrow the selection down further to exclude those records where VIDEOS_VIDEOID = 142.

I tried the following:

select count(videowords.wordid) from videowords group by videowords.wordid having count() < 2 AND videowords.videos_videoid <> '142'

Based off of that first sentence, I see three things wrong with your new query:

1. You're using <>, which essentially means !=, which essentially means not equal, whereas you appear to want the ones that are equal. Which is it?

2. '142' is a string of length three, 142 is an integer. Granted, MySQL isn't really picky about the two, but why not say what you mean?

3. videos_videoid is different than Videos_videoid which is different than VIDEOS_VIDEOID. So.. which is it? Based on your sample output, I would have to guess that the third choice is correct.

[timski72]

I meant to use != and an integer value. It works fine now. Thanks.