Re: Aggregate by calandar weeks?
->From: mcfallry@cps.msu.edu (Ryan L Mcfall)
->Subject: Re: Aggregate by calandar weeks?
->Date: 10 Sep 1993 15:55:13 GMT
->Reply-To: mcfallry@cps.msu.edu (Ryan L Mcfall)
->Organization: Michigan State University
->
->dale.s.walkowicz (dsw@cbnewsm.cb.att.com) wrote:
->: I need to write a query to aggregate numbers by calandar weeks
->: (Mondy to Sunday). It isn't obvous how to do that. Has anyone figured
->: this out before (or figured out it can't be done)? I'd appreciate
->: any help.
->
->: Thanks,
->: Dale
->: dsw@pace.att.com
->
->I have had a simlar problem, but aggregating by month rather than
->by week. If anyone knows an easy way to do this, please post!!
->
->Thanks,
->Ryan
->mcfallry@cps.msu.edu
Given this table:
create table info
( the_date date
, this integer
, that integer
);
You can:
select year(the_date) year, month(the_date) month,
sum(this), count(that), etc., etc.
from info
group by 1, 2
order by 1, 2
;
I haven't tried this with DATETIME columns, but it works well on DATE
columns. I was surprised that I could use 'year' and 'month' as display
aliases, since they are SQL functions, but the aliases work just fine.
Regards,
Alan (BSCS, MSU '70)
+---------------------------+--------------------------------------------+
| R. Alan Popiel | Internet: alan@den.mmc.com |
| Martin Marietta, Tech Ops | Voice: 303-977-9998 |
| P.O. Box 179, M/S 5422 | My opinions may not reflect Martin policy. |
| Denver, CO 80201-0179 USA | In fact, we often disagree. |
+---------------------------+--------------------------------------------+