Re: SQL detail and summary at same time?

[Bill <bubbzbill@xxxxxxxxx>]

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