Oracle uses Analytic Functions to aggregate Subtotal

Select grouping (vsaltype) as sq,

Vsaltype | 'subtotal 'vsaltype,

Sum (amount) as amount,

''Vvin,

''Vpropertywh,

''Vpropertywhdesc

FromSPTW90_INVENTORY_NCS_TMP

Group by rollup (vsaltype)

The result is as follows:

:

Analysis conclusion:

Grouping:

If the SQ value is 0, group by is performed based on vsaltype.

When SQ is set to 1, the subtotal is summarized once, that is, the process of group by rollup (A, B, C) written in other words is group by (A, B, C) ->

Group by (A, B)-> group by (A)-> full table. In this example, only the last two sentences are executed.

Select-1 as sq,

Vsaltype,

Amount,

Vvin,

VPROPERTYWH,

VPROPERTYWHDESC

From SPTW90_INVENTORY_NCS_TMP

The result is as follows:

Analysis conclusion: obtain all detailed data, assign a new virtual field sq, and set sq to-1

In this way:

Select sq, vsaltype, amount, vvin, VPROPERTYWH, VPROPERTYWHDESC

From (select sq, vsaltype, amount, vvin, VPROPERTYWH, VPROPERTYWHDESC

From (select grouping (vsaltype) as sq,

Vsaltype | 'subtotal 'vsaltype,

Sum (amount) as amount,

''Vvin,

''Vpropertywh,

''Vpropertywhdesc

From SPTW90_INVENTORY_NCS_TMP

Group by rollup (vsaltype)

Union all

Select-1 as sq,

Vsaltype,

Amount,

Vvin,

VPROPERTYWH,

VPROPERTYWHDESC

From SPTW90_INVENTORY_NCS_TMP

Where 1 = 1) g

Where g. sq <> 1

Order by vsaltype, sq)

Union all of the rows where sq is 0 and sq is-1

 

When the where condition is specified, obtain data that is not 1 of sq, that is, sq <> 1

So, if you get the final sum, you only need to retrieve the case of sq = 1.

The final model should be like this (Excel exported from Oracle ):