Counting posts in forum

I think you need to drop postDate from group by.

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.

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 ...

Mary

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

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

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

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