Format result from subquery
SQL>
SQL>
SQL> select username,
2 to_number( substr( data, 1, 10 ) ) cnt,
3 to_number( substr( data, 11 ) ) avg
4 from (
5 select a.username, (select to_char( count(*), 'fm0000000009' ) || avg(object_id) from all_objects b where b.owner = a.username) data
6 from all_users a
7 )
8 /
USERNAME CNT AVG
------------------------------ ---------- ----------
SYS 6520 5009.74064
SYSTEM 422 6095.87678
OUTLN 7 1172.57143
DIP 0
TSMSYS 2 8606.5
INV15 2 16237.5
DBSNMP 46 9592.65217
INV10 2 16227.5
CTXSYS 338 9877.92012
XDB 334 10800.7485
ANONYMOUS 0
USERNAME CNT AVG
------------------------------ ---------- ----------
MDSYS 458 11667.2009
HR 34 12104.5
FLOWS_FILES 11 12717.2727
FLOWS_020100 1085 12813.424
JAVA2S 530 16254.6849
INV11 2 16229.5
INV12 2 16231.5
INV13 2 16233.5
INV14 2 16235.5
PLSQL 0
INV16 2 16239.5
USERNAME CNT AVG
------------------------------ ---------- ----------
INV17 2 16241.5
INV18 2 16243.5
INV19 2 16245.5
INV20 2 16247.5
DEFINER 4 16250.5
27 rows selected.
SQL>
Related examples in the same category