SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru
    Join Date
    Dec 2001
    Location
    San Diego, CA
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Counting links in a category

    I have a menu of all the categories for a link directory. However, when I display just the categories, I want to show how many links are in each category.

    For example:

    Flash (5)
    HTML (19)
    JavaScript (12)

    What kind of query do I need to do to pull this info besides a subquery?

  2. #2
    SitePoint Wizard bronze trophy JRMillion's Avatar
    Join Date
    Apr 2004
    Location
    Arlington VA
    Posts
    2,094
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you can use COUNT and GROUP BY to do this.

  3. #3
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As JRMillion pointed out, COUNT and GROUP BY is the way to achieve this.

    Here's a quick example:

    Code:
    SELECT 
    	category_id, 
    	category_name, 
    	COUNT(link_id) AS total 
    FROM 
    	categories 
    LEFT JOIN 
    	links 
    ON 
    	category_id = link_id 
    GROUP BY 
    	category_id, 
    	category_name
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    oh you guys

    count the links in all the subcategories too, please

    if sccr410 is using the "modified preorder tree traversal" or "nested set" model, it's trivial

    with the "adjacency list" model, it is headache #21
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You know what I'm going to do about that, Rudy? I'm just going to enlist in your SQL course.

    Are the classes going to be online or you'll just put the classes on a server as docs for people to downoad? Too bad I'll have to miss the final weeks since I'll be traveling.
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    thanks toly

    the classes are posted once a week for 6 weeks

    there is no exam, eh

    course material remains available after the course for 10 days

    at least, this my understanding

    this will actually be my first course for that site

    if i see you in the discussion board for the site, i will make sure you get all the material one way or another

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sounds good. I'll join when I get home from work.

    Quote Originally Posted by r937
    there is no exam
    I love how that sounds.
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine


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
  •