title: SQL query, how to add count(*) column
body: <p>I have this query:</p>
  
  <pre><code>select segment_name,owner,blocks*8192/1024/1024 as MB,tablespace_name
  from dba_segments
  where segment_name like 'AUD_2%' and owner like 'AUDITOR'
  order by 1 desc;
  
  SEGMENT_NAME     OWNER              MB TABLESPACE_NAME
  ---------------- ---------- ---------- ----------------
  AUD_201304       AUDITOR             7 WSS     
  AUD_201303       AUDITOR            12 WSS     
  AUD_201302       AUDITOR            11 WSS
  </code></pre>
  
  <p>how to add count(*) column ? </p>
  
  <p>I guess a correlated subquery would do, but how exactly ?</p>
  
  <p>Thanks !</p>
  
  <p>sorry found code on stackoverflow, should better search next time. thanks</p>
  
  <p>sorry, here the link to the solution:
  <a href="https://stackoverflow.com/questions/13822512/how-to-count-of-multiple-tables-size-and-tablespace-in-one-query?rq=1">How to count(*) of multiple tables, size and tablespace in one query</a></p>
  
  <p>and here the code:</p>
  
  <pre><code>SELECT ut.table_name,
             to_number(extractvalue(xmltype (dbms_xmlgen.getxml ('select count(*) c from '         ||ut.table_name)),'/ROWSET/ROW/C')) row_count,
         db.blocks*8192/1024/1024 as MB,
         db.tablespace_name
  FROM user_tables ut
    join dba_segments db on db.segment_name = ut.table_name
  WHERE ut.table_name LIKE 'AUD_2%' and owner like 'AUDITOR'
  ORDER BY ut.table_name DESC;
  </code></pre>
  
  <p>and here the output:</p>
  
  <pre><code>TABLE_NAME                      ROW_COUNT         MB TABLES
  ------------------------------ ---------- ---------- ------
  AUD_201304                          21067          7 WSS
  AUD_201303                          43198         12 WSS
  AUD_201302                          39046         11 WSS
  AUD_201301                          44523         17 WSS
  AUD_201212                          50580         15 WSS
  AUD_201211                          49589         14 WSS
  </code></pre>