Page 1 of 1

Problem with multiple tables, using COUNT()

Posted: Thu Mar 25, 2004 5:25 pm
by phice
I've ran into a snag while trying to grab multiple COUNT()'s from different tables.

My query:
SELECT COUNT(p.postid), COUNT(t.threadid), b.boardid, b.display FROM `bmb_boards` b, `bmb_posts` p, `bmb_threads` t WHERE p.boardid = b.boardid AND t.boardid = b.boardid ORDER BY b.boardid ASC
Basically, I want to count the number of postids and threadids that result in the 'p.boardid = b.boardid' and 't.boardid = b.boardid' and grab two columns from the main table (board).

MySQL Error: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause

Posted: Thu Mar 25, 2004 8:55 pm
by Weirdan
I want to count the number of postids and threadids...
Do you want overall counts? Or totals counted per board?

Posted: Thu Mar 25, 2004 10:14 pm
by phice
Count the number of rows from the specified table, that have the same specified boardid.

Posted: Sat Mar 27, 2004 10:57 pm
by phice
Any help...?

Posted: Tue Mar 30, 2004 8:30 pm
by Weirdan

Code: Select all

SELECT COUNT(distinct p.postid), COUNT(distinct t.threadid), b.boardid, b.display FROM `bmb_boards` b, `bmb_posts` p, `bmb_threads` t WHERE p.boardid = b.boardid AND t.boardid = b.boardid GROUP BY b.boardid, b.display ORDER BY b.boardid ASC
Is this what you're looking for?