Re: SQL summary qry

Antonio

It might be possible to get a combination of data and summary in a single SELECT statement - one possibility is using UNION SELECTs in interesting ways. But for this I suggest using QM queries and forms. A QM query will contain the SELECT statement, a QM form will have the layout, including various levels of subtotals.

If you go to a command line, type STRQM - if that works, you have some options - 1 is for queries, 2 for forms - you toggle between a query and its form using F13 once in the editor.

When doing queries, I suggest changing the query creation mode from Prompt to SQL - F19. Prompt is for people just starting with SQL, who are used to Query/400. But you can't do all the good SQL stuff, and you can't use substitution variables, one of the most powerful aspects of QM queries. The editor for forms is similar to that of Query/400, with some nice differences. You have up to 6 break levels, you can edit a character field to wrap within the column width, up to 9 column heading lines, etc.

There's a nice intro called "Query Manager Use" at
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/books/sc415212.pdf

Remember, QM means Query Management (a cross-platform IBM technology), not Query Manager. Query Manager is the interface to Query Management stuff.

There is also a "Query Management Programming" manual at
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/books/sc415703.pdf

That has the best information on how to convert Query/400 queries, as well as details you probably never want to know about forms. It also has info on the various CL commands for QM - GO CMDQM to see what they are. You execute the query using STRQMQRY and name the form on that command.

HTH
Vern

At 01:55 AM 7/6/2007, you wrote:

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.