RE: SQL summary qry

["Wilt, Charles" <WiltC@xxxxxxxxxx>]

As Vernon and I mentioned, Query Manager handles this quite well.

In QM, when you define the form, you simply define it like so:

                            Edit Column Formatting               
            
Type information, press Enter.                                               
For Usage and Edit, press F4 for list.                                       
For Heading, press F4 for prompt.                                            

Column          Heading                    Usage    Edit   Seq  Indent  Width
1               Number                     break1            1     2
2               year                       break2            2     2
3               month                      break3            3     2
4               count                                        4     2
5               Total_Amount               sum               5     2


The SQL statement would look like so:

Select number, year, month, 
       count(*)as cnt, sum(amount) as Total_amount
from myfile 
Group by number, year, month


As you can see, much cleaner than the option 2) example I gave you.

HTH,
Charles
  

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