RE: SQL : subtotals and total

New at V6R1 are data sets, look at
http://www.mcpressonline.com/august-6-2008-vol-7-issue-15_/view-issue.html

This would solve his problem. But yes only at V6R1, sigh.

With regards,
Carel Teijgeler


*********** REPLY SEPARATOR ***********

On 6-8-2008 at 8:26 Wilt, Charles wrote:

The answer is no.

SQL doesn't specifically support totals/subtotals of the data in a results

set.

SQL <> Reporting Tool

Instead, such functionality is left to whatever asked for the data in the

first place. For example,

Query Manager. You create a QM query to get the data and a QM form to

format the results. One thing

you can do with the form is tell it where you want level breaks and

totals.

Having said all that, once and a while it's necessary to simulate such

functionality (but note there

is a performance price to pay).

You can do so with a UNION ALL of the two (or more) queries.

Select ID, Product, Level, sum(number) as total, '01' as rptLevel

From mytable

Group by ID, Product, Level

UNION ALL

Select ID, Product, ' ' as level, sum(number) as total, '02' as rptLevel

From mytable

Group by ID, Product

ORDER BY ID, Product, rptLevel, level

Note the ORDER BY applies to the entire UNION'd result set. Also, when

using UNION, each query must

return the same number and types of columns. Thus, the need for a ' ' as

level in the second.