EasyCFM.COM ColdFusion Forums / MS Access Related Issues / counting posts in forum

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: counting posts in forum

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

grabit
03-09-2007 @ 7:10 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 497
Joined: Jun 2003

Hi Peoples
I have the following query to retrieve data from the db. 2 of the things it has to do is give me the date of the last post and a count of the post records in each of the categories.
The last post date works fine but i cannot get the COUNT(threadID) to return anything but 1 record for each category.
Can someone PLEASE help
query is as follows:
---------------------------
<cfquery name="showcategories" datasource="#dsn#">  
SELECT categories.catID, category, description, MAX(postdate) AS lastpost, COUNT(threadID) as postCount
FROM categories, threads  
where categories.catID = threads.catID  
Group by postDate, categories.catID, category, description  
ORDER BY Category ASC, PostDate DESC  
</cfquery>

by the way it makes no difference if I add the threadID to the group by line
thanks in advance
Grabit

Thanks to you all

This message was edited by grabit on 3-9-07 @ 7:11 PM

kicker
03-09-2007 @ 9:38 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 717
Joined: Oct 2004

Try telling it exactly where you want the count to come from. You can do this through something like this:

count(t.threadID)as threadCount
From categories c, threads t

or just type it all out so it knows exactly which table to pull it from. This is useful if you have two columns named the same in two tables.

Craig
Clearcg.com

grabit
03-09-2007 @ 11:35 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 497
Joined: Jun 2003

Hi Kicker
Nope tried that and it makes no difference whatsoever

Thanks to you all


Website Designed and Developed by Pablo Varando.