Re: sorting by week?

On Tue, Jul 30, 2002 at 11:13:42AM -0700, Phil Glatz wrote:
> What's the best way to group items for weekly summaries? I can group by the
> week of the month, or the week of the year -- suppose I wanted to make a
> report for each week of each month, with most months ending with a partial
> week - is this commonly done, or is the week of the year the most common
> format?

[snip]

> What I'd really like is an easy way to display counts of rows in each week
> of the month, and be able to easily indicate the starting day of the week,
> i.e.
>
> Week | Count
> 06/01/02 | 147
> 06/08/02 | 118
> 06/15/02 | 161
> 06/23/02 | 138
> 06/29/02 | 27
>
> Can this be done in pure SQL? I'm using 7.0.3

The way I usually acheive this is by saying sometihng like:

SELECT datefield - date_part('dow', datefield) as week, count(*)
FROM table
GROUP BY week;

Make sure you're using date fields, not datetime as this trick can do
strange things around the daylight savings transitions.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.