Extract Table name and Count
I do have a table like this shown at input.
In this table i want to extract only this Table Space Name and Count
*******************************************************************************************************************************
Output
*******************************************************************************************************************************
TABLESPACE_NAME Nr of parts
ACCUM_JOURNAL_ENTRIES_DATA 24
ACCOUNT_BALANCES_DATA 24
*******************************************************************************************************************************
Input
*******************************************************************************************************************************
DWSADM ACCUMULATOR_JOURNAL_ENTRIES M200905_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20090601000
DWSADM M200906_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20090701000
TABLE_OWNER TABLE_NAME PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
-------------------- ------------------------------ ------------------------------ ------------------------------ ---------------
DWSADM ACCUMULATOR_JOURNAL_ENTRIES M200907_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20090801000
DWSADM M200908_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20090901000
DWSADM M200909_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20091001000
DWSADM M200910_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20091101000
DWSADM M200911_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20091201000
DWSADM 开发者_Python百科 M200912_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20100101000
DWSADM M201001_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20100201000
DWSADM M201002_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20100301000
DWSADM M201003_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20100401000
DWSADM M201004_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20100501000
DWSADM M201005_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20100601000
DWSADM M201006_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20100701000
DWSADM M201007_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20100801000
DWSADM M201008_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20100901000
DWSADM M201009_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20101001000
DWSADM M201010_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20101101000
DWSADM M201011_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20101201000
DWSADM M201012_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20110101000
DWSADM M201101_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20110201000
DWSADM M201102_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20110301000
DWSADM M201103_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20110401000
DWSADM M201104_ACCUM_JOU_ENT ACCUM_JOURNAL_ENTRIES_DATA 20110501000
****************************** ------------------------------
Nr of parts 24
DWSADM ACC_ACCOUNT_BALANCES M200905_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20090601000
DWSADM M200906_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20090701000
DWSADM M200907_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20090801000
DWSADM M200908_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20090901000
DWSADM M200909_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20091001000
DWSADM M200910_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20091101000
DWSADM M200911_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20091201000
DWSADM M200912_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20100101000
DWSADM M201001_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20100201000
DWSADM M201002_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20100301000
DWSADM M201003_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20100401000
TABLE_OWNER TABLE_NAME PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
-------------------- ------------------------------ ------------------------------ ------------------------------ ---------------
DWSADM ACC_ACCOUNT_BALANCES M201004_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20100501000
DWSADM M201005_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20100601000
DWSADM M201006_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20100701000
DWSADM M201007_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20100801000
DWSADM M201008_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20100901000
DWSADM M201009_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20101001000
DWSADM M201010_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20101101000
DWSADM M201011_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20101201000
DWSADM M201012_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20110101000
DWSADM M201101_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20110201000
DWSADM M201102_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20110301000
DWSADM M201103_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20110401000
DWSADM M201104_ACC_ACC_BAL ACCOUNT_BALANCES_DATA 20110501000
****************************** ------------------------------
Nr of parts 24
your question is not very clear, although it seems a simple GROUP BY would work here:
SELECT tablespace_name, COUNT(*) nb_of_parts
FROM your_table
GROUP BY tablespace_name
精彩评论