SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2002
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can MySQL get you the number of rows that aren't repeated because we used GROUP BY?

    I have a number of uses if i can manage to do this.

    As I understand, you use GROUP BY when you want to prevent the same row being returned multiple times. An example of this for a message board where you want to get the topic data and the content of the last post in the last five topics posted. You use GROUP BY on the topicid column and each topic is only returned once. What I want to know is if there is some way to use COUNT() or some similar feature to determine the number of posts in each topic that is being GROUP'd.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, there is a way
    Code:
    select topics.id
         , topics.subject
         , count(posts.topicid) as postcount
      from topics
    left outer
      join posts
        on topics.id
         = posts.topicid
    group
        by topics.id
         , topics.subject
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2002
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well thank you sir, you do seem to have all the answers.


Bookmarks

Posting Permissions

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