Page 1 of 1

Help with query

Posted: Wed Nov 21, 2007 9:49 am
by QbertsBrother
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


i have this table.

Code: Select all

messageid  	int(11)
userid  	int(11)
entered  	datetime
viewed  	datetime
status  	varchar(255)
what i am trying to do is select all userid's and find out how many times they viewed something so i did this:

Code: Select all

SELECT `userid` , count( `viewed` )
FROM `eblast_usermessage`
GROUP BY `userid`
LIMIT 0 , 30 
that gave me this:

Code: Select all

userid 	count( `viewed` )
1 	22
2 	35
3 	14
4 	48
5 	10
6 	20
7 	2
8 	2
9 	3
10 	30
11 	1
12 	0
13 	5
14 	5
15 	0
16 	0
17 	3
now i believe that is what i want. what i want to do now is get the userid where count( `viewed` ) is <= 1. so i tried this:

Code: Select all

SELECT `userid` , count( `viewed` )
FROM `eblast_usermessage`
where count( `viewed` ) <=1
GROUP BY `userid`
i got an error about the group function.

any help would be great

thanks


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Wed Nov 21, 2007 10:13 am
by aaronhall

Code: Select all

SELECT `userid` , count( `viewed` ) as viewCount
FROM `eblast_usermessage`
WHERE viewCount <=1
GROUP BY `userid`

Posted: Wed Nov 21, 2007 10:22 am
by QbertsBrother
thanks for the help.

when i edited the query i got this error

Code: Select all

 SELECT `userid` , count( `viewed` ) AS viewCount
FROM `eblast_usermessage`
WHERE viewCount <=1
GROUP BY `userid`

MySQL said: Documentation
#1054 - Unknown column 'viewCount' in 'where clause'

Posted: Wed Nov 21, 2007 10:37 am
by aaronhall
Try:

Code: Select all

SELECT `userid` , count( `viewed` ) as viewCount
FROM `eblast_usermessage`
GROUP BY `userid`
HAVING viewCount <=1

Posted: Wed Nov 21, 2007 10:43 am
by QbertsBrother
thank you very much

that worked great.