RE: SQL group by on result fields

You can group by datefield, but it does not aggregate. For example, if I
have data of

2002-11-19   3
2002-11-19   4
2002-11-19   5
2002-11-20   1
2002-11-20   2

Select year(datefield), month(datefield), sum(number) ... Group by datefield

Grouping by datefield gives the following:

2002   11   12
2002   11   3

It is grouping on like values of the date field. If for exemple I had data
for November 1-20, I would have 20 rows of output rather than 1. This
happens because you are grouping on the date field itself, not the month()
and year() of the datefield. The work data will look like this:

2002-11-19   12
2002-11-20   3

Then apply the month(), year()

2002   11   12
2002   11   3

Since we have already grouped by the datefield, the query is complete.



To get the correct  output of

2002   11   15

You must use the group by month(datefield), year(datefield). The work data
will be split into the month and year parts, then the number part is summed.


Work data 1:

2002-11-19   12
2002-11-20   3

Apply year(), month()

2002   11   12
2002   11   3

Apply group by year(), month()

2002   11   15

I saw the reply from Don, but wanted to give a clearer explanation of why
this does not work.

Loyd


-----Original Message-----
From: Carel Teijgeler [mailto:coteijgeler@chello.nl]
Sent: Wednesday, November 20, 2002 11:34 AM
To: midrange-l@midrange.com
Subject: Re: SQL group by on result fields


Don,

try: group by datefield , datefield

Regards,
Carel Teijgeler


*********** REPLY SEPARATOR  ***********

On 20-11-02 at 9:58 Fisher, Don wrote:

>I would like to summarize some fields based on the year and month of a
>date type field.  SQL allows me to use: "Select
>year(datefield),month(datefield),numeric1 from file" but will not allow me
>to use "Select year(datefield),month(datefield),sum(numeric1) from file
>group by year(datefield),month(datefield)".