Re: SQL summary qry

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

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.