RE: SQL group by on result fields

Don,

I'm not positive that this will work on V4R1, but its worth a shot...

With Slt1 as (Select year(datefield) as DatYr,
                           month(datefield) as DatMo,
                           numeric1
                  from file)
Select DatYr, DatMo, Sum(numeric1)
>From Slt1
Group By DatYr, DatMo

hth,

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863




-----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

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.