Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Posts
    124

    Unanswered: returning total records using count

    I have the following query:

    SELECT TOP 5 *
    FROM dbo.Weblog
    ORDER BY blogDate DESC

    I have another table "UserComments" with a foreign key field "blogID" related to the primary key field "blogID" in the table "Weblog".

    I'm trying to alter the above query to count all the records related by the "blogID" field from both tables.

    I want to return the total record count for all user comments for a specific "blogID" field in the Weblog table.

    For example, if the blogID field in the Weblog table is 22 then I want to return the count for all records that contain 22 in the "blogID" field in the UserComments table.

    I was trying this:
    SELECT TOP 5 * , (SELECT COUNT(*) FROM dbo.UserComments) AS TotalRecords
    FROM dbo.Weblog
    ORDER BY blogDate DESC

    which returned all the records in the UserComments table instead of the count related by the "blogID" field.

    How can I return the correct count by specific "blogID" instead all the records in the UserComments table?

    Thanks for any help.
    -Dman100-

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    Try this :

    SELECT dbo.UserComments.blogID, count (*)
    FROM dbo.UserComments, dbo.Weblog
    WHERE dbo.UserComments.blogID = dbo.Weblog.blogID
    GROUP BY dbo.UserComments.blogID;

    Regards,

    RBARAER

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use a left outer join so that you get blogs without comments too
    Code:
    select top 5 
           W.blogName
         , W.blogTitle
         , W.blogDate  
         , count(C.blogID) as comments
      from dbo.Weblog as W
    left outer
      join dbo.UserComments as C
        on W.blogID
         = C.blogID
    group
        by W.blogName
         , W.blogTitle
         , W.blogDate  
    order 
        by W.blogDate desc
    note that you cannot say "select star" when you use GROUP BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by r937
    note that you cannot say "select star" when you use GROUP BY
    Sorry, but in Oracle that works perfectly. Maybe I'm becoming too much Oracle-SQL-Oriented... Is it not standard ANSI SQL ?

  5. #5
    Join Date
    Jan 2004
    Posts
    124
    Thanks for the reply! Using the LEFT OUTER JOIN is getting very close, but I am unable to include the field "blogComment" in the query because the data type is set to text in the database. Is there a way around this?

    This is what I have so far:

    SELECT TOP 5
    W.blogDate,
    W.blogHeader,
    W.blogPostTime,
    COUNT(C.blogID) AS Comments
    FROM dbo.tblWeblog AS W
    LEFT OUTER
    JOIN dbo.tblUserComments AS C
    ON W.blogID = C.blogID
    GROUP BY
    W.blogDate,
    W.blogHeader,
    W.blogPostTime
    ORDER BY
    W.blogDate DESC

    So, if I include the filed "blogComment" then I get an error related to the text data type.

    SELECT TOP 5
    W.blogDate,
    W.blogHeader,
    W.blogComment,
    W.blogPostTime,
    COUNT(C.blogID) AS Comments
    FROM dbo.tblWeblog AS W
    LEFT OUTER
    JOIN dbo.tblUserComments AS C
    ON W.blogID = C.blogID
    GROUP BY
    W.blogDate,
    W.blogHeader,
    W.blogComment,
    W.blogPostTime
    ORDER BY
    W.blogDate DESC

    Thanks again for your help!
    -Dman100-

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by RBARAER
    Sorry, but in Oracle that works perfectly. Maybe I'm becoming too much Oracle-SQL-Oriented... Is it not standard ANSI SQL ?
    okay, you're right, let me be more specific

    if you use GROUP BY, you must itemize the columns to group on

    furthermore, since you must group on all non-aggregate columns in the SELECT list, you might as well write them out there as well

    typically, "select star" gets you into way more trouble than it's worth

    and with most people using a text editor (rather than command line input), it becomes trivial to copy/paste the list of columns that you want

    i mean, since you have to itemize them in the GROUP BY anyway
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by -Dman100-
    ...I am unable to include the field "blogComment" in the query
    the solution to this problem is that you don't want to

    if it's a TEXT field, and you want 5 of them, are you really going to display all 5 fields? no

    so use left(W.blogComment,50) as teaser or something
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by r937
    typically, "select star" gets you into way more trouble than it's worth
    Do you only mean it is cleaner to specify columns on which one does the count instead of using * ? I would say it depends on the type of count one wants to do, isn't it ? If one wants to use a DISTINCT count on specific fields, then OK, but if one wants to count ALL rows for which (field1, field2) is the same, then why not write :

    select field1, field2, count(*) from table1 group by field1, field2;

    ?

    Excuse me, but I don't see the "trouble" you're talking about when using count(*). Could you please give me a precise example ?

    Regards,

    RBARAER

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what i meant by "select star" is SELECT *

    in this specific example, you should not say
    Code:
    select W.*
         , count(C.blogID) as comments
      from ...
    group
        by W.blogName
         , W.blogTitle
         , W.blogDate
    because, as we discovered, the "star" includes a TEXT field which causes an error ("gets you into trouble") even if you do also code it into the GROUP BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    as to your other question, count(*) gives you the wrong answer when you are counting in a left outer join and there are no matched records

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Sorry for having misunderstood your point, r937. I agree with you on the "select *". Anyway, this talk will have taught me something : I must admit I'm not quite used to using OUTER JOIN, that's why I was not seeing any trouble in using count(*). Now I see.

    Thank you and best regards,

    RBARAER

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •