CODEWITHSUNDEEP
sqloracle-databaserollup
sm86
sm86

Reputation: 87

group by rollup duplicating results

Hi I am trying to use group by rollup to get a grand total for a column but when I do it is duplicating the rows. What am I doing wrong? Thanks.

enter image description here

Code:

WITH TOTS AS
(
select 
swkl.prn_cln_n,
swkl.swkl_bgn_dt,
swkl.swkl_end_dt,
swkl.sec_id,
actbl.*
from actbl
join actblc on actblc.actbl_seq_id = actbl.actbl_seq_id
join swkl on swkl.swkl_id = actblc.swkl_id 
where swkl.prn_cln_n = '242931'
and swkl.stlm_rcd_sta_cd = 'A'
and trunc(swkl.stlm_rcd_mntd_ts) = to_date('03/10/2021','mm/dd/yyyy')   --'14-JUN-2021'
and actblc.actblc_wkfl_pcs_cd = 'COM'
and actblc.stlm_rcd_sta_cd = 'A'
)
,F2 AS
(
SELECT PRN_CLN_N AS CLIENT_NUMBER   
,LINE_TYPE_HM   
,PR_TYP_C   
,SUM(BIIL_A) AS BILL_AMOUNT
FROM TOTS
GROUP BY PRN_CLN_N  
,LINE_TYPE_HM   
,PR_TYP_C
)
SELECT coalesce(CLIENT_NUMBER,'TOTAL') AS CLIENT_NUMBER
,LINE_TYPE_HM
,PR_TYP_C
,SUM(BILL_AMOUNT)  BILL_AMOUNT
FROM F2
group by rollup (CLIENT_NUMBER
,LINE_TYPE_HM
,PR_TYP_C)

Upvotes: 0

Views: 729

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

There's nothing wrong, as far as I can tell.

When using rollup, you specify columns in it - client_number, line_type_hm, pr_typ_c which is 3 column. rollup will produce 3 + 1 = 4 levels of subtotals. Those subtotals are visually identified by having NULL values in rollup columns. In your screenshot,

  • 1st level are all "fully populated" lines (those that have all columns with some values, i.e. the 1st line, 3rd, 5th, ...)
  • 2nd level are lines whose pr_typ_c is NULL (2nd line, 4th, 6th, ...)
  • 3rd level is the penultimate row (has both line_type_hm and pr_typ_c empty)
  • 4th level is "grand total", the last line with TOTAL in client_number column

I don't have your tables nor data so it is you who might know what to do next because you can reduce number of subtotals by performing partial rollup. How? For example,

group by client_number, rollup(line_type_hm, pr_typ_c)

or any other combination of rollup columns.

Try it and see what happens.

Upvotes: 1

Related Questions