greetings experts, I haven't seen this matter among other questions, certainly won't mind having it pointed out if I've missed it.

I have an awkward requirement from management that requires summing a set of values, and each value is often going to be a repeated amount within a set of records, and I need to accumulate only one occurrence. I need to sum at the cat level the unique (among cat+cid) unit_max values, other values on the table records are GROUP BY factors or other aggregated values, can't be discarded. The awkward requirement has already placed the value for unit_max on each record, and it is the maximum of the unit value for all the records within the subgroup cat+cid.

CREATE TABLE category_test
(
    cat             VARCHAR2(1),
    cid             NUMBER(3),
    unit            NUMBER(4),
    unit_max        NUMBER(4)
);

INSERT INTO category_test values ('A' cat,111 cid,30 unit,45 unit_max FROM dual);
INSERT INTO category_test (SELECT 'A' cat,111 cid,30 unit,45 unit_max FROM dual);
INSERT INTO category_test (SELECT 'A' cat,111 cid,45 unit,45 unit_max FROM dual);

INSERT INTO category_test (SELECT 'B' cat,111 cid,30 unit,50 unit_max FROM dual);
INSERT INTO category_test (SELECT 'B' cat,111 cid,50 unit,50 unit_max FROM dual);
INSERT INTO category_test (SELECT 'B' cat,111 cid,20 unit,50 unit_max FROM dual);
INSERT INTO category_test (SELECT 'B' cat,222 cid,20 unit,20 unit_max FROM dual);

INSERT INTO category_test (SELECT 'C' cat,111 cid,45 unit,45 unit_max FROM dual);
INSERT INTO category_test (SELECT 'C' cat,222 cid,45 unit,45 unit_max FROM dual);
COMMIT;

This code is a beginning on what I need, but clearly not achieving the desired result for the outcome on unit_max:

SELECT  cat,
        sum(unit) new_unit,
        sum(UNIQUE unit_max) new_max 
FROM    category_test
GROUP BY cat;

GETTING:

C        NEW_UNIT         NEW_MAX
- --------------- ---------------
A             105              45
B             120              70
C              90              45

NEED HELP TO GET:

                    -- second value determination:
A     105     45    -- 'A'+111 -> 45
B     120     70    -- 'B'+111 -> 50, 'C'+222 -> 20
C      90     90    -- 'B'+111 -> 45, 'C'+222 -> 45

SQL> select c.*,
  2         case when row_number() over ( partition by cat, cid order by rowid ) = 1
  3           then unit_max end as new_unit_max
  4  from   category_test c;

C        CID       UNIT   UNIT_MAX NEW_UNIT_MAX
- ---------- ---------- ---------- ------------
A        111         30         45           45
A        111         30         45
A        111         45         45
B        111         30         50           50
B        111         50         50
B        111         20         50
B        222         20         20           20
C        111         45         45           45
C        222         45         45           45

9 rows selected.

SQL> SELECT  cat,
  2          sum(unit) new_unit,
  3          sum(new_unit_max) new_max
  4  FROM
  5  (
  6  select c.*,
  7         case when row_number() over ( partition by cat, cid order by rowid ) = 1
  8           then unit_max end as new_unit_max
  9  from   category_test c
 10  )
 11  GROUP BY cat;

C   NEW_UNIT    NEW_MAX
- ---------- ----------
A        105         45
B        120         70
C         90         90

3 rows selected.