RE: SQL summary qry

Antonio,

There are two ways to accomplish what you want...

1) Have the application running the SQL statement do the totaling from the
"detail" rows returned by
the SQL.

2) Use multiple SQL statements, one for each level, with a UNION ALL between
them.


Note that "detail" just means the lowest level of information in this case.

Option 1) is the most common and probably the best performing, but I've used 2)
a few times.

A true query tool for generating reports such as Query Manager or SEQUAL, or
whatever includes the
ability to do the totaling in option 1). Interactive tools such as Run SQL
Scripts or STRSQL do not.



HTH,
Charles Wilt

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Antonio
Fernandez-Vicenti
Sent: Friday, July 06, 2007 2:55 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL summary qry

Jim, Vernon and Birgitta, let me enter this thread since I'm
interested in an additional detail:
Apart from the comments about using names, asc/desc, etc...
Can you get the grouping at, say, two or more levels of grouping?

I mean, (grouping) giving totals for, at
number_field/year/month level, as in Vern's SELECT sentence,
... PLUS ...
PLUSand also higher level (number_field/year , and
number_field) Totals in same SELECT in an "interspersed"
(did I spell it right?) way as shown below.

Note: I've edited results with blank lines in between and
horizontal displacements at different level totals just to
make things more clear, but that would not be needed , of course!

Number Year Month Count Total amount
001 1998 11 xx 99.99
001 1998 12 xx 999.99

001 1998 1099.98

001 1999 11 xx 99.99
001 1999 12 xx 999.99

001 1999 1099.98

001 2199.96

002 1999 11 01 200.01
002 1999 06 55 1234.56

002 1999 1434.57

002 1434.57

TIA,
--------------------------------------------------------------
--------------------------------
Vernon Hamberg wrote:

select number_field,
year(date_field) as year_field,
month(date_field) as month_field,
count(*) as num_occurrences,
sum(amount_field') as total_amt
from somelib/somefile
group by number_field, year(date_field), month(date_field)
order by 1, 2, 3

--
Antonio Fernandez-Vicenti
afvaiv@xxxxxxxxxxxxxx


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

This e-mail transmission contains information that is intended to be
confidential and privileged. If you receive this e-mail and you are not a
named addressee you are hereby notified that you are not authorized to read,
print, retain, copy or disseminate this communication without the consent of
the sender and that doing so is prohibited and may be unlawful. Please reply
to the message immediately by informing the sender that the message was
misdirected. After replying, please delete and otherwise erase it and any
attachments from your computer system. Your assistance in correcting this
error is appreciated.