Can´t get Select Count(*)... GROUP BY to work

[Thomas]

Hi!

I´m a newbie with this and I´m trying to build a forum of my own but
have stumbled on my first problem as early as the opening page.

To the problem:
I want to show a simple forum layout with tables looking something like
this

Forum Name | Topics | Posts | Last Post
---------------------------------------------------
General | 1 | 4 | Webmaster, 09 Feb -06

To do this I use the following SQL;
sqlPosts = "SELECT Date, COUNT(Date) as NrOfPosts FROM ForumPosts WHERE
ForumID = " & rsForum("ForumID") & " GROUP BY Date ORDER BY Date DESC"

But this doesn´t give me the right numbers. Instead of showing Topics to
be 1 and Posts to be 4 it shows Topics to be 1 and Posts to 1.

But if I use a simpler sql, like this;
sqlPosts = "SELECT COUNT(Date) as NrOfPosts FROM ForumPosts WHERE
ForumID = " & rsForum("ForumID") & " ORDER BY Date DESC"

Then it works, but I can´t get any other info from the table, like date,
userid etc.

I use this simple asp to call on the count and the date:
DatePosted = rsPosts("Date")
NrOfPosts = rsPosts("NrOfPosts")

What am I doing wrong? is it the asp or the sql?

 

[Bob Barrows [MVP]]

Hi!

I´m a newbie with this and I´m trying to build a forum of my own but
have stumbled on my first problem as early as the opening page.

To the problem:
I want to show a simple forum layout with tables looking something
like this

Forum Name | Topics | Posts | Last Post
---------------------------------------------------
General | 1 | 4 | Webmaster, 09 Feb -06

To do this I use the following SQL;
sqlPosts = "SELECT Date, COUNT(Date)

It is a bad idea to use reserved keywords for database object names. This
practice can lead to very hard to diagnose errors.
Do yourself a favor and change "Date" to "PostDate". You won't regret it.
http://www.aspfaq.com/show.asp?id=2080

as NrOfPosts FROM ForumPosts
WHERE ForumID = " & rsForum("ForumID") & " GROUP BY Date ORDER BY
Date DESC"

But this doesn´t give me the right numbers. Instead of showing Topics
to be 1 and Posts to be 4 it shows Topics to be 1 and Posts to 1.

<snip>

What database type and version are you using (never leave this information
out of a database-related question).
What is the datatype of the "Date" column? If using Access, don't bother
telling us the value of Format property, which is irrelevant (the Format
only controls how the information in the column is displayed, not how it is
stored). Only tell us whether its a varchar (text) or datetime (Date/Time)
column.

Does your database system have a native query tool like the Access Query
Builder or the SQL Server Query Analyzer? If so, fire it up and use it to
execute this query:

SELECT distinct [Date] FROM ForumPosts WHERE ForumID = ...

Does it result in the expected entries for each date? I suspect that it
doesn't. Show us a few rows from the resultset of that query.

Bob Barrows

 

[Dave Anderson]

It is a bad idea to use reserved keywords for database object names.
This practice can lead to very hard to diagnose errors.
Do yourself a favor and change "Date" to "PostDate". You won't regret
it. http://www.aspfaq.com/show.asp?id=2080

While I agree with your premise, "Date" is not a reserved keyword in T-SQL.
http://msdn.microsoft.com/library/en-us/tsqlref/ts_ra-rz_9oj7.asp



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

 

[Bob Barrows [MVP]]

While I agree with your premise, "Date" is not a reserved keyword in
T-SQL.
http://msdn.microsoft.com/library/en-us/tsqlref/ts_ra-rz_9oj7.asp

No, but it is in ODBC (see Aaron's list) and, I think, OLEDB ... yes:
http://msdn.microsoft.com/library/en-us/oledb/htm/oledbidbinfo__getkeywords.asp

Because it is on these lists, queries containing that word can run into
problems when being run via ADO.

And even though it is not on the current reserved keyword list, I've heard
vague rumors that a future version of SQL will have a Date datatype (date
only). It almost happened in SQL 2005 but problems with its implementation
caused it to be left out.

Bob Barrows

 

[Bob Barrows [MVP]]

I don't see how -- perhaps because I do everything in JScript and
therefore do not rely on such things as default properties or take
syntactic shortcuts like:

rs("Date")

There have been many cases in these two newsgroups (many of which i have
answered personally) where the attempt to execute a query containing the
word "Date" has caused errors. However, most ... no, maybe all ... of these
cases involved Jet, where the keyword is definitely reserved due to the
existence of the VBA Date() function.
So i will concede that with the current version of SQL Server, it is
probably not be an issue.

Well, that is a valid concern. And "Date" is certainly a loaded word
in any language. But don't we have to work with what we have? I mean,
if I choose the unreserved "Flurb" today, what guarantee do I have
that no future version of the language will need it?

Conceded. However, as you say, "Date" is "loaded", and it was a datatype in
a beta of SQL2005. "Flurb", on the other hand ...

Bob

 

[Bob Barrows [MVP]]

Well, that is a valid concern. And "Date" is certainly a loaded word
in any language. But don't we have to work with what we have? I mean,
if I choose the unreserved "Flurb" today, what guarantee do I have
that no future version of the language will need it?

Oh! You must have missed the announcement of the addition of the Flurb
function to the next version of T-SQL ...
;-)

 

[Patrice]

Have you tried COUNT(*) instead of COUNT(Date) as mentioned in the subject
line ? It looks like I don't find this attempt in the message body...

 

[Bryan V]

try reading this:

http://blog.209software.com/2005/01/ora-00937-not-single-group-group.html

It might have to do with the locktype you're using