RE: SQL summary qry

I decided I needed to add a little more info about option 2).

First off it usually helps to add a field named LEVEL or something.

For your example output, something like so would work:

Select 1 as level, digits(number) as nbr, digits(year) as yr, digits(month) as
mth,
char(count(*)) as cnt, sum(amount) as amt
from myfile
Group by number, year, month,
UNION ALL
Select 2 as level, digits(number) as nbr, digits(year) as yr, ' ' as mth,
' ' as cnt, sum(amount) as amt
from myfile
Group by number, year
UNION ALL
Select 3 as level, digits(number) as nbr, ' ' as yr, ' ' as mth,
' ' as cnt, sum(amount) as amt
from myfile
Group by number

Order by nbr, yr, mth, level


Notice, every column from each select in a UNION has to have the same type,
thus to get the blank
values, I needed to convert numeric values to character.

Also, the ORDER BY clause is used only once and applies to the entire UNIONed
results set.

Lastly, you don't have to give everything a name, if you don't then you can
just use the column number
in the ORDER BY cluse like so:
ORDER BY 2, 3, 4, 1

HTH,
Charles Wilt

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Wilt, Charles
Sent: Friday, July 06, 2007 8:18 AM
To: Midrange Systems Technical Discussion
Subject: 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.

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