SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru dragonhawk's Avatar
    Join Date
    Apr 2002
    Location
    Melbourne
    Posts
    707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    select the number of times a particular member ID appears (was "Help with SQL")

    I'm trying to get some information from a table.

    Tablename
    -----------
    ID
    MemberID

    ID is the primary key
    MemberID is not a primary key. It can have duplicate values.

    What I'm after is get an SQL query to select the number of times a particular member ID appears in the table.

    So if the MemberID rows contains member id, 4,6,2,3,2,6,2,2 then I want the sql query to return 2 in one column and 4 in another (since 4 is the number of times 2 appears), 3 in one column and 1 in the next, 4 in one column and 1 in the next, 6 in one column and 2 in the next.. Although what would be ideal is if I get returned the member id number and the total times they appear in 2 columns.

    Thanks for any help

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    pretty easy. what have you tried so far for a query? Hint you need a count and a group by.

  3. #3
    SitePoint Guru dragonhawk's Avatar
    Join Date
    Apr 2002
    Location
    Melbourne
    Posts
    707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's the problem... I don't even know where to start for this type of query coz I don't understand the 'group by' command.

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    have you read the manual for the GROUP BY command? Have you viewed the examples in the manual for the GROUP BY command? what part of it is not clear?

  5. #5
    if ($zee == "Guru") { $zee--;}
    Join Date
    Nov 2005
    Location
    Karachi - Pakistan
    Posts
    1,133
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select memberid,count(*) from tablename group by memberid;

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well you've handed out the fish, did you teach the person how to fish?

    this looks suspiciously like a homework question, no attempt at a query was shown and no explanation was given as to what isn't clear in the manual, it is why I was trying to find out what was known, rather than hand out an answer.


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
  •