Re: SQL detail and summary at same time?

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

> From: Vernon Hamberg
>
> 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.

Not to contradict, but the answer I provided in the other forum wasn't a
variation on yours, since it didn't use UNION at all.  It created a CTE
(Common Table Expression) with the totals and then JOINed the totals to the
original table.  Since the original question basically wanted one row in the
result per row in the original table (just with extra total data on one row
per tuple), that was the more straightforward solution.

I think your UNION technique is more appropriate for this question, since
the question looks like it wants extra rows for the subtotals and final
totals.

Joe