select query with count

Dev Shed Forums Sponsor:

January 23rd, 2003, 10:22 PM

rob5408

Certified Genius

Join Date: Aug 2001

Location: over there

Posts: 77

Time spent in forums: 46 m 57 sec
Reputation Power: 12

select query with count

this may be simple but i haven't yet been able to figure it out...i have three tables invloved in this query...one for forums, one for the threads, and one for posts...the format is like this...

forums
forumID | forumTitle

threads
threadID | threadForum

posts
postID | postThread

all the IDs are auto increasing integers. threadForum is a foreign key that links a certain thread to which forum it is part of and postThread is a foreign key that links it to a thread...a thread has a series of posts related to it. with the lowest postID being the first post in the thread obviously. now how do i return a recordset with the rows being each forums title, a count of how many threads there in the forum, and a count of how many posts there are in a forum? the main problem i'm getting is that i can't get an aggregate function like count() to go with a row for each forum...does this involve two subqueries somehow?? anyone got any tutorials that will help? rob

__________________
Delenda est Carthago

January 24th, 2003, 02:57 AM

pabloj

Modding: Oracle MsSQL Firebird

Dev Shed God 8th Plane (8500 - 8999 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 8,527

Time spent in forums: 3 Months 1 Week 1 Day 3 h 19 m 49 sec
Reputation Power: 537

What is the query you are trying to do?

SELECT forum.title, count(threads.threadID), count (posts.postID) FROM (posts INNER JOIN threads ON posts.postThread = threads.threadID) INNER JOIN forum ON threads.threadForum = forum.forumID GROUP BY posts.postID, threads.threadID

Could something like this help?

__________________
My blog
Tutorials about OSS databases, DBMonster ...
Contribute to OSS Development, fill bug reports!
Developer Shed eSupport
Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK)
An introduction to database normalization
Natural or Surrogate key
Custom ordering for your results
Correlated and uncorrelated subqueries
Don't turn your outer joins into inner joins
Random data (with a bias)

January 24th, 2003, 02:56 PM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 2,351

Time spent in forums: 1 Month 4 Days 8 h 2 m 22 sec
Reputation Power: 390

Code:

select title,
  (select count(*) 
      from threads 
     where threadforum = f.forumid ) 
as threads,
  (select count(*) 
      from posts inner join threads
    on posts.posthread = threads.threadid
    where threads.threadforum = f.forumidid )
 as posts
  from forum f

Viewing: Dev Shed Forums > Databases > Database Management > select query with count

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump