Re: SQL detail and summary at same time?

[Antonio Fernandez-Vicenti <afvaiv@xxxxxxxxxxxxxx>]

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!
--------------------------------------------------------------
Bill escribió:

> Antonio Fernandez-Vicenti wrote:
>  
>
> > Thinking about Query/400, you can define a query to bring detail lines 
> > as well as summary data. Of course, once defined, you can select to run 
> > the query just for summary only, or execute showing detail lines and 
> > summary data in between. In any case, you get Grand Totals at the end.
> >  
> >    
> To be more accurate, Query -reports- both detail and summary.  That is 
> also the easier answer to your question, just create a detail SQL 
> statement and the report writer will report the summary as well.
>
> To actually get both in an SQL statement can be done, but it'd be quite 
> a bit of work.  Basically, you join a summarized data set to your 
> detail.  Here's one way:
>
> With SumData(Select field1, field2, sum(field3) as sumfield  From 
> somefile Group by field1, field2)
> Select dtl.field1, dtl.field2, dtl.field3, sumdata.field3
> From somefile dtl join SumData  on dtl.field1=sumdata.field1 and 
> dtl.field2=sumdata.field2
>
> Bill
>