sorting by week?
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?
I'm currently grouping by aliases like
TO_CHAR(date_field,'WW MM/DD/YY') AS fday
and stripping out the week and date parts when I display them
I also tried doing a by day grouping to check the values returned by the
'W' and 'WW' formats, with TO_CHAR(lt_modified,'MM/DD/YY W WW') AS fday
05/31/02 4 21 | 5
06/01/02 0 21 | 6
06/02/02 1 22 | 3
Why is there a zero for the month week on 6/1?
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
thanks!
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 | 27Can 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@svana.org> http://svana.org/kleptog/
Show quoted text
There are 10 kinds of people in the world, those that can do binary
arithmetic and those that can't.