Re: SQL detail and summary at same time?

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

Yes you CAN do it - use a UNION SELECT in one way of doing it -to get 
subtotals and final total you need 3 SELECTs in the UNION - you need 
some kind of column to sort on that will put detail and summary and 
final total in the right order - not that hard to do, just divide the 
problem into its components and conquer it. I just answered a similar 
question in the MIDRANGE-L list, I believe - Joe Pluta has a 
variation on it. That requirement had the subtotal on the last record 
of each group - nice little poser.

HTH
Vern
At 05:12 PM 1/17/2007, you wrote:

> You slightly misunderstood my answer.  If you indeed want your output as
> your first example, you just can't do it - that is the output of a
> report writer.  SQL is only a data access program, it doesn't do what
> you want.
>
> Bill
>
>
> Antonio Fernandez-Vicenti wrote:
> > Bill, thanks for your answer, but I think it's not what I meant:
> > I'd like to get something like this
> >         01          AA        field3
> >         01          AA        field3
> >         01          AA        field3
> >           Subtotal01AA   xxxxx
> >         01          BB        field3
> >         01          BB        field3
> >         01          BB        field3
> >           Subtotal01BB   yyyyy
> >         05          XX       field3
> >         05          XX       field3
> >         05          XX       field3
> >           Subtotal05XX  zzzzzz
> >            Final TOTAL   TTTT
> >
> > with Subtotals intermixed with detail lines, plus a final Grand Total
> >
> > whereas the code you suggested  I think will yield something like
> >
> >         01          AA        field3    xxxxx
> >         01          AA        field3    xxxxx
> >         01          AA        field3    xxxxx
> >         01          BB        field3    yyyyy
> >         01          BB        field3    yyyyy
> >         01          BB        field3    yyyyy
> >         05          XX       field3     zzzzz
> >         05          XX       field3     zzzzz
> >         05          XX       field3     zzzzz
> > and without the Final Total.
> >
> > Am I wrong or did I misunderstand your suggestion?
> > Thanks in any case!
>
> --
> This is the PC Technical Discussion for iSeries Users (PcTech) mailing list
> To post a message email: PcTech@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/pctech
> or email: PcTech-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/pctech.