help with GROUP BY query

Hi, I am not a newbie to general SQL queries but i do get confused with the complicated ones.
I have a table of applicants. I want to be able to count applications over the course of a month but group them by week. does that make sense?
So essentially i would have 4 records for the query: week 1's total, week 2's total. etc..
Is this possible in one query? or do I need to create a query for each week with the date range i need?

Thank you for any help you can give!

Nate

use this:

CREATE TABLE `test` (
`applicant` int(11) NOT NULL default '0',
`created` datetime NOT NULL default '0000-00-00 00:00:00'
);
insert into test values
(1076459140, '2004-02-10 19:25:40'),
(1076451177, '2004-02-10 17:13:02'),
(1076446248, '2004-02-10 15:50:48'),
(1076440334, '2004-02-10 14:12:14'),
(1076533658, '2004-02-11 16:07:44'),
(1076534374, '2004-02-11 16:19:34'),
(1076537250, '2004-02-11 17:07:36'),
(1076540161, '2004-02-11 17:56:01'),
(1076575058, '2004-02-12 03:37:38'),
(1076579118, '2004-02-12 04:45:18'),
(1076593243, '2004-02-12 08:40:43'),
(1076651093, '2004-02-13 00:44:53'),
(1076651601, '2004-02-13 00:53:21'),
(1076875244, '2004-02-15 15:00:57'),
(1076881440, '2004-02-15 16:44:00'),
(1076901842, '2004-02-15 22:24:02'),
(1076901919, '2004-02-15 22:25:19'),
(1076912878, '2004-02-16 01:28:11'),
(1076913284, '2004-02-16 01:34:58');

select sum( if(date_format(created,'%Y%m%d') between 20040210 and 20040212 ,1,0)) period1,
sum( if(date_format(created,'%Y%m%d') between 20040213 and 20040216 ,1,0)) period2
from test;

So you ll use week periods to ..
I hope you Enjoy it...

oraclable, You are a Genius! Thank you, I was able to fit this query into my table strucutre.

Thanks again.

Using the sample table oraclable posted, this is how you would print an application count by week in a single query. It's a little complicated so I'll try to break it down and explain it.

To make this work we have to convert the date to the number of days since 01-01-0000, then divide by 7 and truncate. This will get all the records in the same week to have a common value to group on.

Code:

---

SELECT
  COUNT(*) as cnt,
  FROM_DAYS(
    FLOOR((TO_DAYS(created)
            + 1 // change this number to change the first day of the week
    )/7)*7
    -1 // MUST match the number you changed above
  ) AS startdate,
  FROM_DAYS(FLOOR((TO_DAYS(created) + 1)/7)*7-1) + INTERVAL 1 WEEK AS enddate
  // what's inside the FROM_DAYS() should always match the startdate calculation
FROM test
GROUP BY startdate

---