Format result from subquery : Virtual Table « Subquery « Oracle PL / SQL






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

1.Select from virtual table (sub query) with rownum
2.First three rows from subquery