Problem with multiple tables, using COUNT()

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
phice
Moderator
Posts: 1416
Joined: Sat Apr 20, 2002 3:14 pm
Location: Dallas, TX
Contact:

Problem with multiple tables, using COUNT()

Post 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
Image Image
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

I want to count the number of postids and threadids...
Do you want overall counts? Or totals counted per board?
User avatar
phice
Moderator
Posts: 1416
Joined: Sat Apr 20, 2002 3:14 pm
Location: Dallas, TX
Contact:

Post by phice »

Count the number of rows from the specified table, that have the same specified boardid.
Image Image
User avatar
phice
Moderator
Posts: 1416
Joined: Sat Apr 20, 2002 3:14 pm
Location: Dallas, TX
Contact:

Post by phice »

Any help...?
Image Image
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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?
Post Reply