www.webdeveloper.com
Results 1 to 3 of 3

Thread: [RESOLVED] SQL COUNT Error

  1. #1
    Join Date
    Oct 2004
    Location
    Waterford, CT
    Posts
    17

    resolved [RESOLVED] SQL COUNT Error

    I'm using the following select statement to pull in info from a topic and a count of the replies made to that topic. But it doesn't give me a row in the select table for any topics that have no replies ... shouldn't it give a 0 for the COUNT???

    Code:
    SELECT DISTINCT
     t.ID AS tID, 
     t.topTitle AS tTitle,
     t.topDescription AS tDesc,
     t.topCategory AS tCat,
     COUNT(r.repTopic) AS tPosts
    FROM
     topics t INNER JOIN
     replies r
    ON
     r.repTopic = t.ID
    GROUP BY
     t.ID,
     t.topTitle,
     t.topDescription,
     t.topCategory

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    you should only have 1 piece in your group by clause, and it should be r.repTopic.

    But honestly I've seen better results using something like this:

    Code:
    SELECT DISTINCT
     t.ID AS tID, 
     t.topTitle AS tTitle,
     t.topDescription AS tDesc,
     t.topCategory AS tCat,
     (SELECT COUNT(repTopic) from replies WHERE repTopic = t.ID) AS tPosts
    FROM
     topics t
    Only because when you do the count, you don't really get a good benefit from the join. Let me know if either of those work.

  3. #3
    Join Date
    Oct 2004
    Location
    Waterford, CT
    Posts
    17

    Smile

    Thank you so much. It worked like a charm.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles