RE: SQL group by on result fields

What you are trying definitely works on V4R5. This sql:

select year(labordate), month(labordate), sum(daydirh)
from prodwork
group by year(labordate), month(labordate)

Produced correct results:

YEAR ( LABORDATE )  MONTH ( LABORDATE )  SUM ( DAYDIRH )
           2,001                   1            90,463.3
           2,001                   2            85,931.8
           2,001                   3            90,608.9
           2,001                   4            88,431.7
           2,001                   5            95,261.0
           2,001                   6            97,486.7
           2,001                   7            82,622.1
           2,001                   8           104,364.3
           2,001                   9            94,238.7
           2,001                  10           100,900.3
           2,001                  11            83,404.8
           2,001                  12            70,632.0
           2,002                   1           100,959.4
           2,002                   2            94,586.7

Sorry this doesn't help your current situation....

Loyd


-----Original Message-----
From: Fisher, Don [mailto:Dfisher@roomstoreeast.com]
Sent: Wednesday, November 20, 2002 8:59 AM
To: 'midrange-l@midrange.com'
Subject: SQL group by on result fields


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)".

I have tried using "group by 1,2" and using the "as" clause to name the
fields yearfield and monthfield to no avail.  I know I can create an
intermediate file with the year and month fields and then get the totals
from that, but it seems like there should be a way to do this without the
extra step.

By the way, I also tried something from the archives to the effect of: "with
temp as (select year(datefield),month(datefield)..." but my system didn't
like the "with".

We are on V4R1.

Any help will be greatly appreciated.

Donald R. Fisher, III
Project Manager
The Roomstore Furniture Company
(804) 784-7600 extension 2124
DFisher@roomstoreeast.com