469,904 Members | 2,273 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,904 developers. It's quick & easy.

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

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?

*** Sent via Developersdex http://www.developersdex.com ***
Feb 16 '06 #1
8 4551
Thomas wrote:
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
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Feb 16 '06 #2
Bob Barrows [MVP] wrote:
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


While I agree with your premise, "Date" is not a reserved keyword in T-SQL.
http://msdn.microsoft.com/library/en...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.
Feb 16 '06 #3
Dave Anderson wrote:
Bob Barrows [MVP] wrote:
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


While I agree with your premise, "Date" is not a reserved keyword in
T-SQL.
http://msdn.microsoft.com/library/en...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...etkeywords.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
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Feb 16 '06 #4
Bob Barrows [MVP] wrote:
While I agree with your premise, "Date" is not a reserved
keyword in T-SQL.
No, but it is in ODBC (see Aaron's list) and, I think, OLEDB
... yes:

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


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


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?

--
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.
Feb 17 '06 #5
Dave Anderson wrote:
Bob Barrows [MVP] wrote:
While I agree with your premise, "Date" is not a reserved
keyword in T-SQL.
No, but it is in ODBC (see Aaron's list) and, I think, OLEDB
... yes:

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


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.


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


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
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Feb 17 '06 #6
Dave Anderson wrote:
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 ...
;-)
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Feb 17 '06 #7
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...

--
Patrice

"Thomas" <no****@devdex.com> a écrit dans le message de
news:%2***************@TK2MSFTNGP15.phx.gbl...
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?

*** Sent via Developersdex http://www.developersdex.com ***

Feb 20 '06 #8
try reading this:

http://blog.209software.com/2005/01/...oup-group.html

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


Apr 17 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by M. Mehta | last post: by
9 posts views Thread by Kelvin | last post: by
15 posts views Thread by Simon Gare | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.