By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,984 Members | 2,794 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,984 IT Pros & Developers. It's quick & easy.

Counting posts in forum

P: 22
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
Mar 10 '07 #1
Share this Question
Share on Google+
7 Replies


MSeda
Expert 100+
P: 159
I think you need to drop postDate from group by.
Mar 10 '07 #2

P: 22
I think you need to drop postDate from group by.
i tried that but without it in there i get the following
Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'PostDate' as part of an aggregate function.
Mar 10 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
i tried that but without it in there i get the following
Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'PostDate' as part of an aggregate function.
That's because you have it in the ORDER BY. You have also used a full outer join. Try this instead ...

Expand|Select|Wrap|Line Numbers
  1. SELECT categories.catID, category, description, 
  2. MAX(postdate) AS lastpost, COUNT(threadID) as postCount 
  3. FROM categories INNER JOIN threads   
  4. ON categories.catID = threads.catID   
  5. GROUP BY categories.catID, category, description   
  6. ORDER BY Category ASC, lastpost DESC   
  7.  
Mary
Mar 10 '07 #4

P: 22
That's because you have it in the ORDER BY. You have also used a full outer join. Try this instead ...

Expand|Select|Wrap|Line Numbers
  1. SELECT categories.catID, category, description, 
  2. MAX(postdate) AS lastpost, COUNT(threadID) as postCount 
  3. FROM categories INNER JOIN threads   
  4. ON categories.catID = threads.catID   
  5. GROUP BY categories.catID, category, description   
  6. ORDER BY Category ASC, lastpost DESC   
  7.  
Mary
Hi Mary thanks for the reply
when i use that code i get the following error - can this be fixed and if so how please?

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

The error occurred in C:\CFusionMX\wwwroot\Forum\mainPage.cfm: line 22

20 : <cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
21 :
22 : <cfquery name="showcategories" datasource="#dsn#">
23 : SELECT categories.catID, category, description,
24 : MAX(postdate) AS lastpost, COUNT(threadID) as postCount



--------------------------------------------------------------------------------

SQL SELECT categories.catID, category, description, MAX(postdate) AS lastpost, COUNT(threadID) as postCount FROM categories INNER JOIN threads ON categories.catID = threads.catID GROUP BY categories.catID, category, description ORDER BY Category ASC, lastpost DESC
DATASOURCE forum
VENDORERRORCODE -3010
SQLSTATE 07002

thanks Grabit
Mar 11 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
This is usually caused by a naming problem in one of the fields. Try running the query directly on the database and see what happens. If it still doesn't work check all the field and table names are correct. If there is no problem there then remove the Order By and see if it runs.

Mary
Expand|Select|Wrap|Line Numbers
  1. SELECT categories.catID, category, description, 
  2. MAX(postdate) AS lastpost, COUNT(threadID) as postCount 
  3. FROM categories INNER JOIN threads 
  4. ON categories.catID = threads.catID 
  5. GROUP BY categories.catID, category, description 
  6. ORDER BY Category ASC, lastpost DESC
  7.  
Mar 11 '07 #6

P: 22
This is usually caused by a naming problem in one of the fields. Try running the query directly on the database and see what happens. If it still doesn't work check all the field and table names are correct. If there is no problem there then remove the Order By and see if it runs.

Mary
Expand|Select|Wrap|Line Numbers
  1. SELECT categories.catID, category, description, 
  2. MAX(postdate) AS lastpost, COUNT(threadID) as postCount 
  3. FROM categories INNER JOIN threads 
  4. ON categories.catID = threads.catID 
  5. GROUP BY categories.catID, category, description 
  6. ORDER BY Category ASC, lastpost DESC
  7.  

Thanks very very very much Mary
took out the order by line and shes away laughing
your a genius. As you will be aware i am a newbie to this sql thing and to find someone who knows what they are doing and are willing to help, is a breath of fresh air on the web today
Again
Thankyou
Grabit
Mar 11 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks very very very much Mary
took out the order by line and shes away laughing
your a genius. As you will be aware i am a newbie to this sql thing and to find someone who knows what they are doing and are willing to help, is a breath of fresh air on the web today
Again
Thankyou
Grabit
You're welcome.

Mary
Mar 11 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.