SQL> select a,b from test2;
A
B
---------- ----------
2 34
2 34
3 12
3 34
1
1
2
1
2
1
3
1
3
1
3
1
11 rows selected
SQL> select distinct a,'null' count from test2 where b is null
2union
3select a,c from (
4SELECT A, B,
5 to_char(SUM(B) OVER(PARTITION BY A)) c, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B nulls first) rn
6FROM TEST2
7) where rn=1 and b is not null
8;
A COUNT
---------- ----------------------------------------
1 null
2 68
3 46
SQL>
|