Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Canīt get Select Count(*)... GROUP BY to work

Reply
Thread Tools

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

 
 
Thomas
Guest
Posts: n/a
 
      02-16-2006
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 ***
 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-16-2006
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"


 
Reply With Quote
 
 
 
 
Dave Anderson
Guest
Posts: n/a
 
      02-16-2006
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.


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-16-2006
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.


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-17-2006
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"


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-17-2006
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.


 
Reply With Quote
 
Patrice
Guest
Posts: n/a
 
      02-20-2006
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" <(E-Mail Removed)> a écrit dans le message de
news:%23IvV$(E-Mail Removed)...
> 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 ***



 
Reply With Quote
 
Bryan V
Guest
Posts: n/a
 
      04-17-2006
try reading this:

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

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




 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
select gridview row without using select button? =?Utf-8?B?RGFiYmxlcg==?= ASP .Net 5 02-27-2009 02:56 AM
select of select box will select multiple in another box palmiere Javascript 1 02-09-2004 01:11 PM
Can't get a code group to work Chris Cummings ASP .Net Security 3 11-03-2003 03:30 PM
<xsl:value-of select="count('/GROUPS/GROUP')"/> does not work. Son KwonNam XML 1 10-14-2003 04:34 AM



Advertisments