Results 1 to 4 of 4

Thread: help with GROUP BY query

  1. #1
    Join Date
    Jan 2006
    Posts
    7

    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

  2. #2
    Join Date
    Apr 2005
    Location
    florida
    Posts
    89
    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...

  3. #3
    Join Date
    Jan 2006
    Posts
    7
    oraclable, You are a Genius! Thank you, I was able to fit this query into my table strucutre.

    Thanks again.

  4. #4
    Join Date
    Jan 2006
    Posts
    6

    Single query solution

    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
    Last edited by wickning1; 01-31-2006 at 11:30 AM. Reason: Correctness

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •