CODEWITHSUNDEEP
sqloracle-databasepivotgrouping
John
John

Reputation: 503

SQL Dynamic Grouping or Pivot and subtotals

I need to create a dynamic grouping table in SQL that would look similar to this:

Customer    Rating    Asset Type    Exposure
ABC         9-        apples        -10
                      bananas        20
                      subtotal       10
DEF         5         grapes          5
GHI         8+        apples         15
                      bananas         9
                      subtotal       25

I have tried pivots and left joins but i am getting what i need.

Code

SELECT 
A.CUSTOMER
,A.FRR as Rating
,C.PRODUCT_TYPE
,SUM(D.UTILIZATION) as Exposure
from table1 A
LEFT JOIN table1 B ON A.CUSTOMER = 
B.CUSTOMER and A.PRODUCT_TYPE = 'Apples'
LEFT JOIN table1 C ON A.CUSTOMER = 
C.CUSTOMER and A.PRODUCT_TYPE = 'Bananas'
LEFT JOIN table1 D ON A.CUSTOMER = 
D.CUSTOMERand A.PRODUCT_TYPE = 'Grapes'
GROUP BY A.CUSTOMER
,A.FRR
,C.PRODUCT_TYPE
;

any help/direction you can provide would be great!

Upvotes: 0

Views: 465

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65373

You can use lag() analytic functions with rollup(customer, frr, product_type) :

with t1 as
(
select customer, frr, product_type, sum(utilization) as exposure,
       lag(customer) over (order by customer,product_type) as lg_cst,
       lag(frr) over (order by customer,product_type) as lg_frr
  from table1
 group by rollup(customer, frr, product_type) 
), t2 as
(
select 
       case when nvl(lg_cst,'') = customer 
       then 
            null
       else   
            customer
       end as customer,
       case when nvl(lg_frr,'') = frr 
       then 
            null
       else   
            frr
       end as frr2,
       case when nvl(lg_frr,'') = frr and product_type is null and customer is not null then 
              'subtotal'
            when customer is null then
              'Grand Total'
            else 
               product_type
       end as product_type, exposure
  from t1
)
select *
  from t2
 where product_type is not null;

Demo

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74690

Oracle GROUP BY supports ROLLUP and GROUPING SETS that will provide the broader aggregations for you:

So your base query looks like:

SELECT 
  ... 
GROUP BY 
  A.CUSTOMER,A.FRR,C.PRODUCT_TYPE

And it produces:

Customer    Rating    Asset Type    Exposure
ABC         9-        apples        -10
ABC         9-        bananas        20
DEF         5         grapes          5
DEF         5         apples         15
DEF         5         bananas         9

If you change it to group by a grouping set:

SELECT 
  ... 
GROUP BY GROUPING SETS (
  (A.CUSTOMER,A.FRR,C.PRODUCT_TYPE),(A.CUSTOMER,A.FRR)
)

You'll get extra rows that subtotal the fruit, and the fruit name is NULL to indicate it's a total (Actually the GROUPING() function indicates if it's "null because it's a total" or "null because the underlying data really is null" but..):

Customer    Rating    Asset Type    Exposure
ABC         9-        apples        -10
ABC         9-        bananas        20
ABC         9-        NULL           10     <-- total of fruit for ABC/9-
DEF         5         grapes          5
DEF         5         apples         15
DEF         5         bananas         9
DEF         5         NULL           29    <-- total of fruit for DEF/5

GROUP BY ROLLUP(A.CUSTOMER,A.FRR,C.PRODUCT_TYPE) would do a similar thing, except it goes all the way up to the root from right to left (you get totals rows like ABC NULL NULL 45, DEF NULL NULL 62 and NULL NULL NULL 107

Footnote: I've made some assumptions that your example data has errors - I couldn't see how 5+15+9 added up to 25, or whether DEF and GHI should be the same etc. Finally, I wouldn't attempt to hide data on rows if it's the same as the row above, because that then means the only thing keeping the data set meaningful is the order. It's OK to do in the front end just before the user sees it, but doing it in a back end, where it might be passed aropund several places before it's used, means any one of those places could lose the order and wreck the meaning of the data. For more discussion, see a very similar answer I just made here: Get count of particular category and further bifurcated sub category using SQL query

If you're still desperate to hide data in column X on row N that is the same as data in column X of row N-1, you can use the LAG analytic function to pull the previous row data and make a decision on whether to null it if it's the same as this row's data

You asked about not totalling if there was only one row anyway. In these circumstances we could perhaps do something like count the number of detail rows and exclude those rows where it's a totals row and the number of details rows it summarises is 1

To do this add the following line to the end of the query:

HAVING NOT (GROUPING(PRODUCT_TYPE) = 1 and COUNT(DISTINCT PRODUCT_TYPE) = 1)

For more on this, see the db fiddle: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=935992f52f067c84b2899e2acea688a0

Essentially how it works is:

  • GROUPING(PRODUCT_TYPE) returns 0 for a details row and 1 for a subtotal row
  • COUNT(DISTINCT PRODUCT_TYPE) returns 1 for a details row and N for a totals row where N is the number of distinct assets that were rolled into that total:
    • 10 rows of "apple" generate a COUNT() of 10 but a COUNT(DISTINCT) of 1.
    • 5 rows of "apple" and 5 rows of "orange" generate a COUNT() of 10 and a COUNT(DISTINCT) of 2.

By excluding the rows where the count of distinct asset types was 1 AND the row is a totals row, we suppress totals rows that essentially repeat the details row

Upvotes: 1

Related Questions