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

Discussion in 'ASP General' started by Thomas, Feb 16, 2006.

  1. Thomas

    Thomas Guest

    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 ***
    Thomas, Feb 16, 2006
    #1
    1. Advertising

  2. 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"
    Bob Barrows [MVP], Feb 16, 2006
    #2
    1. Advertising

  3. 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-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.
    Dave Anderson, Feb 16, 2006
    #3
  4. 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-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
    --
    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.
    Bob Barrows [MVP], Feb 16, 2006
    #4
  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"
    Bob Barrows [MVP], Feb 17, 2006
    #5
  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.
    Bob Barrows [MVP], Feb 17, 2006
    #6
  7. Thomas

    Patrice Guest

    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" <> a écrit dans le message de
    news:%23IvV$...
    > 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 ***
    Patrice, Feb 20, 2006
    #7
  8. Thomas

    Bryan V Guest

    Bryan V, Apr 17, 2006
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Son KwonNam
    Replies:
    1
    Views:
    895
    Dimitre Novatchev
    Oct 14, 2003
  2. efelnavarro09
    Replies:
    2
    Views:
    935
    efelnavarro09
    Jan 26, 2011
  3. Chris Cummings

    Can't get a code group to work

    Chris Cummings, Nov 3, 2003, in forum: ASP .Net Security
    Replies:
    3
    Views:
    101
    Chris Cummings
    Nov 3, 2003
  4. Xeno Campanoli
    Replies:
    1
    Views:
    343
    James Britt
    Jul 1, 2005
  5. palmiere
    Replies:
    1
    Views:
    400
    Erwin Moller
    Feb 9, 2004
Loading...

Share This Page