SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Fountain Valley, California
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query count(). Help-a-nub

    Is it possible to get the count() for "forums, "threads" and "messages" extrapolating from the following query? Thanx for helping a newb.

    select
    sf_conferences.id,
    sf_conferences.name
    from
    ((#variables.tableprefix#conferences
    left JOIN sf_forums ON sf_conferences.id=sf_forums.conferenceidfk)
    left JOIN sf_threads ON sf_forums.id = sf_threads.forumidfk)
    left JOIN sf_messages ON sf_threads.id = sf_messages.threadidfk
    GROUP BY
    sf_conferences.id,
    sf_conferences.name

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    extrapolating from???
    Code:
    SELECT sf_conferences.id
         , sf_conferences.name 
         , COUNT(f.id)    AS forums
         , SUM(f.threads) AS threads
         , SUM(f.msgs)    AS msgs
      FROM sf_conferences 
    LEFT OUTER
      JOIN ( SELECT sf_forums.id
                  , sf_forums.conferenceidfk 
                  , COUNT(t.id) AS threads
                  , SUM(t.msgs) AS msgs
               FROM sf_forums
             LEFT OUTER
               JOIN ( SELECT sf_threads.id
                           , sf_threads.forumidfk
                           , COUNT(sf_messages.threadidfk) AS msgs
                        FROM sf_threads
                      LEFT OUTER
                        JOIN sf_messages
                          ON sf_messages.threadidfk = sf_threads.id
                      GROUP
                          BY sf_threads.id
                           , sf_threads.forumidfk
                    ) AS t
                 ON t.forumidfk = sf_forums.id
             GROUP
                 BY sf_forums.id
                  , sf_forums.conferenceidfk 
           ) AS f
        ON f.conferenceidfk = sf_conferences.id
    GROUP 
        BY sf_conferences.id
         , sf_conferences.name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    add the following to your field list:
    Code:
    count(distinct sf_forums.id) as forumcount
    count(distinct sf_threads.id) as threadcount
    count(sf_messages.id) as messagecount
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Fountain Valley, California
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much r037 and longneck.
    Good lesson indeed.
    wConti


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
  •