Problem with Count() 1

[snowboardr]

I am having trouble getting the Group By to work... the count is off now matter what group by I use... can you help point out what the issue is, and tell me more about group by... i always have trouble with it...

sql= "SELECT "_
& "COUNT(post.pid) as PostCount,"_
& "post.ptid,"_
& "post.ptid2,"_
& "post.puid,"_
& "post.psubject,"_
& "post.pbody,"_
& "post.ptype,"_
& "post.preplycount,"_
& "post.ptime,"_
& "post.psticky,"_
& "post.ptimereply,"_
& "user.uid,"_
& "user.uname,"_
& "MATCH(psubject,pbody,uname) AGAINST ('+" & qsearch & "' in boolean mode)" _
& " FROM forumpost as post join users user on user.uid=post.puid WHERE MATCH(psubject,pbody,uname) AGAINST ('" & qsearch & "' in boolean mode) group by user.uid"

 

[r937]

the GROUP BY that you should be using will consist of all non-aggregate columns in the SELECT

SELECT COUNT(post.pid) as PostCount
     , post.ptid2
     , post.puid
     , post.psubject
     , post.pbody
     , post.ptype
     , post.preplycount
     , post.ptime
     , post.psticky
     , post.ptimereply
     , user.uid
     , user.uname
     , MATCH(psubject, pbody, uname) 
       AGAINST ('+" & qsearch & "' 
           in boolean mode)
  FROM forumpost as post 
  join users user 
    on user.uid=post.puid 
 WHERE MATCH(psubject, pbody, uname) 
       AGAINST ('" & qsearch & "' 
           in boolean mode) 
group 
    by [b]post.ptid2
     , post.puid
     , post.psubject
     , post.pbody
     , post.ptype
     , post.preplycount
     , post.ptime
     , post.psticky
     , post.ptimereply
     , user.uid
     , user.uname
     , MATCH(psubject, pbody, uname) 
       AGAINST ('+" & qsearch & "' 
           in boolean mode)[/b]

however, this is pretty awkward, and may not even work if any of those columns are TEXT

it looks like you are displaying a post with some sort of count, so if you could explain what you are actually trying to count, i might be able to come up with a saner alternative

for more information on why the GROUP BY should consist of all non-aggregate columns in the SELECT, see 12.10.3 GROUP BY with Hidden Fields

r937.com | rudy.ca

 

[snowboardr]

thanks for your help, yes a few of them are text:

   post.psubject
   post.pbody
   user.uname

What would be the best way to go about this? This is for a forum correct, its for searching posts I must count the records found before hand, so I can do paging...

- Jason

 

[r937]

sorry, not enough info

your query retrieves detail rows for all posts that meet a certain MATCH criterion, so what are you trying to count?

r937.com | rudy.ca

 

[snowboardr]

I am trying to count all posts that match the Search terms the user types I probably could group by post.ptype However i switched it over so it searches Main posts and replies If its a main post then post.ptype=0 if its a reply post.ptype=1

There is also a preplyid so i can show replies under the main post...

What else would you need to know?

 

[r937]

i don't get it

you are returning all rows that match a certain condition

why not use the number of rows returned to tell you how many rows were returned?

every scripting language has a function for this purpose

r937.com | rudy.ca

 

[snowboardr]

I wish i knew how to do that... haha i hate counts... do you know how to do it in ASP?

 

[snowboardr]

actually... I have to count it first, because im showing page links above the actual content loop

 

[r937]

i don't do asp, but 10 seconds on google gave me .GetRows

http://www.adopenstatic.com/faq/recordcountalternatives.asp#preferred3

r937.com | rudy.ca

 

[snowboardr]

ya i never could get getrows to work right, i just tried it again and it errors... but i cant use it because im displaying my page numbers above the loopage.

 

[r937]

maybe you should start a new thread in the asp forum

r937.com | rudy.ca

 

[snowboardr]

well either way i have to get this count working... and you know what will happen if I say mysql in the ASP forum.. they will tell me to come back here..

 

[r937]

then don't say mysql, because that has nothing to do with your question

just say "i have a query which returns X rows, how do i get the number X in asp after running the query but before printing out the rows?"

r937.com | rudy.ca