Jump to content


Photo

Counting records by date


  • Please log in to reply
3 replies to this topic

#1 CGRRay

CGRRay
  • New Members
  • Pip
  • Newbie
  • 9 posts

Posted 11 October 2006 - 04:23 AM

I have a table full of members who have joined our organization over the past several years and continue to join and renew. Their membership lasts for a specific amount of time, usually 1 year or 6 months. I need to write a query that will tell me how many members there were at the end of each month by chapter, for each month of this year.

All the records are stored in two tables called member and chapter. The fields in member are: member_id, first_name, last_name, date_joined, date_renewed, date_expires, chapter_id. The fields in chapters are: chapter_id, member_id, title.
I tried using this:
SELECT chapter.title,
(SELECT COUNT(*)
FROM member
WHERE member.chapter_id = chapter.chapter_id
AND member.date_expires > 1138751999) AS members
FROM chapter
ORDER BY chapter.title;
  but then realized that I was counting all the people who joined or renewed after Jan 31 too.
I sure would appreciate some help.
Thanks

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 October 2006 - 07:51 PM

Why not use a BETWEEN clause?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 CGRRay

CGRRay
  • New Members
  • Pip
  • Newbie
  • 9 posts

Posted 13 October 2006 - 04:30 PM

Thanks for responding. I found a different way to do this:
SELECT chapter.title,
(SELECT COUNT(*)
FROM member
WHERE member.chapter_id = chapter.chapter_id
AND member.date_expires > 1146441599) AS members_as_of_Mar31,
(SELECT COUNT(*)
FROM member
WHERE member.chapter_id = chapter.chapter_id
AND member.date_expires BETWEEN 1146441600 AND 1162339199) AS expired_after_Mar31,
(SELECT COUNT(*)
FROM member
WHERE member.chapter_id = chapter.chapter_id
AND member.date_joined BETWEEN 1146441600 AND 1162339199) AS joined_after_Mar31,
(SELECT COUNT(*)
FROM member
WHERE member.chapter_id = chapter.chapter_id
AND member.date_renewed BETWEEN 1146441600 AND 1162339199) AS renewed_after_Mar31
FROM chapter
ORDER BY chapter.title;

The results in MySQL Query Browser look correct. The problem is that this query works in MySQL Query Browser but not in my live version of MySQL. I get this error:
#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 '' at line 1

Any Ideas?
Thnaks again


#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 13 October 2006 - 07:27 PM

Not really... that's a rather undescriptive error.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users