Problem counting records

[CGRRay]

I'm trying to count members in chapters from the table cl_member and order them by their chapter. This is the query I'm trying to use. For some reason it works in MySQL Query Browser but I get this error message when I run it using phpMyAdmin.


#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(member_id)
FROM cl_member
WHERE cl_member.chapte


Here's the query:



SELECT cl_chapter.title, (
SELECT COUNT( member_id )
FROM cl_member
WHERE cl_member.chapter_id = cl_chapter.chapter_id
AND cl_member.date_expires >1146441599
) AS members_as_of_Mar31, (
SELECT COUNT( member_id )
FROM cl_member
WHERE cl_member.chapter_id = cl_chapter.chapter_id
AND cl_member.date_expires
BETWEEN 1146441600
AND 1162339199
) AS expired_after_Mar31, (
SELECT COUNT( member_id )
FROM cl_member
WHERE cl_member.chapter_id = cl_chapter.chapter_id
AND cl_member.date_joined
BETWEEN 1146441600
AND 1162339199
) AS joined_after_Mar31, (
SELECT COUNT( member_id )
FROM cl_member
WHERE cl_member.chapter_id = cl_chapter.chapter_id
AND cl_member.date_renewed
BETWEEN 1146441600
AND 1162339199
) AS renewed_after_Mar31
FROM cl_chapter
ORDER BY cl_chapter.title

Any help would be greatly appreciated.
Ray

[vladimir_cz]

Hi!

Please read documentation for MySQL selects (http://dev.mysql.com/doc/refman/4.1/en/select.html)
did you see any complex selects like you wrote?

[vladimir_cz]

just for make your life simle, try to use grouping, something like this

select year(join_date),month(join_date),count(*) from table group by year(join_date),month(join_date)