query count(). Help-a-nub

[Walt]

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

[r937]

extrapolating from???

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

[longneck]

add the following to your field list:

count(distinct sf_forums.id) as forumcount
count(distinct sf_threads.id) as threadcount
count(sf_messages.id) as messagecount

[Walt]

Thank you very much r037 and longneck.
Good lesson indeed.
wConti