SitePoint Sponsor |
|
User Tag List
Results 1 to 4 of 4
Thread: query count(). Help-a-nub
-
Apr 9, 2008, 02:08 #1
- 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
-
Apr 9, 2008, 06:43 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Apr 9, 2008, 06:44 #3
- 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
-
Apr 9, 2008, 08:34 #4
- 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