Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2008
    Posts
    53

    Unanswered: 2 x COUNT() in one query from different tables

    Tables:

    sections:
    id_section
    section

    threads:
    id_thread
    id_section
    thread

    posts:
    id_post
    id_thread
    post

    1 section can have many threads. 1 thread can have many posts.

    Is it possible to get number of posts and number of threads each section using 1 query. Something like on this forum:
    dBforums - Database Support Community (we can see number of posts and number of threads each section)

    I have tried:
    Code:
    SELECT a.section, COUNT( b.id_thread ) , COUNT( c.id_post ) 
    FROM sections a, threads b, posts c
    WHERE a.id_section = b.id_section AND b.id_thread = c.id_thread
    GROUP BY a.id_section;
    But it shows the same number of threads as number of posts what is wrong for each section.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Try

    COUNT( DISCINT b.id_thread )

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT sections.section
         , COUNT(threads.id_section)       AS section_thread_count
         , COALESCE(SUM(t.thread_posts),0) AS section_post_count 
      FROM sections 
    LEFT OUTER
      JOIN threads 
        ON threads.id_section = sections.id_section 
    LEFT OUTER
      JOIN ( SELECT id_thread
                  , COUNT(*) AS thread_posts
               FROM posts
             GROUP
                 BY id_thread ) AS t
        ON t.id_thread = threads.id_thread
    GROUP 
        BY sections.section
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2008
    Posts
    53
    THX You both.
    This is very interesting - both solutions works !! I thought about something like r937's solution (which is very professional) but shammat's solutions also works and this is strange for me - only DISTINCT - strange

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •