more complex GROUP BY query

[Natcon67]

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

[chazzy]

could you describe the table maybe, explain what's being put in each column?

group by typically groups items in a column that have the same value. if anything, yours would require a subquery.

[Natcon67]

Thanks for replying.
For all intents and purposes this is what the table looks like.

app_id (int) | app_name (varchar) | app_date (date)
+------------------------------------------------------------
| 1 John Doe 2005-12-01
| 2 Jane Doe 2005-12-03
| 3 John Smith 2005-12-05
| 4 Jane Smith 2005-12-07
| 5 Mark Smith 2005-12-09
| 6 Mary Smith 2005-12-11
| 7 John Doe 2005-12-13
| 8 Jane Doe 2005-12-15
| 9 John Smith 2005-12-17
| 10 Jane Smith 2005-12-19
| 11 Mark Smith 2005-12-21
| 12 Mary Smith 2005-12-30

Essentially what I would like is for the results to look like this:

4 rows
-------------------------------------------
week | COUNT(app_id)
-------------------------------------------
1 4
2 3
3 4
4 1
-------------------------------------------

or something close to that. I could easily figure it out with 4 separate queries for each month (1 query for each week) but it would be nice to do it in one.

Thanks again.

[sridhar_423]

There might be even simpler query..try this one..

select count(*),(case when to_number(to_char(app_date,'dd'))<7 then 1
else (case when to_number(to_char(app_date,'dd'))<14 then 2
else (case when to_number(to_char(app_date,'dd'))<21 then 3 else 4 end)
end)
end) week
from TABLE_NAME where
trunc(app_date) between to_date('01-10-2005','dd-mm-yyyy') and to_date('31-10-2005','dd-mm-yyyy')
group by
(case when to_number(to_char(app_date,'dd'))<7 then 1
else (case when to_number(to_char(app_date,'dd'))<14 then 2
else (case when to_number(to_char(app_date,'dd'))<21 then 3 else 4 end)
end)
end)

[Natcon67]

By the Way, i am using MySQL. I think that some of your functions dont work with MySQL.

[chazzy]

here's the thing, you have static data (it seems). i can guess what defines a week, but in theory you'd have to be rewriting it a lot. December technically has 4.4 weeks, not just 4. without a marker of somekind to say which week it is, the query won't be able to tell you this. you also would end up with uneven weeks.

as for the query posted, it works. you can drop the function calls to to_number() in mysql, and insert of to_char(app_date,'dd') use date_format(app_date,'%d') and finally this line:

trunc(app_date) between to_date('01-10-2005','dd-mm-yyyy') and to_date('31-10-2005','dd-mm-yyyy')

should be passed in from your application, this is what determines what month to look at. you can try using this, assuming your app passes in the date as MM-YYYY


date_format(app_date,'%c-'01'-%Y') = 'mm-YYYY'


Also, for anyone's reference, if you make a date in mysql, it doesn't default to 1, it defaults to 0 (ie I got it to give me a date of 01/00/2006)