Unanswered: 2 x COUNT() in one query from different tables
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:
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.
, COUNT(threads.id_section) AS section_thread_count
, COALESCE(SUM(t.thread_posts),0) AS section_post_count
ON threads.id_section = sections.id_section
JOIN ( SELECT id_thread
, COUNT(*) AS thread_posts
BY id_thread ) AS t
ON t.id_thread = threads.id_thread
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