title: Median in Pivot in Oracle
body: <p>I am trying to find median in the pivot block but i keep on getting the below error as pivot needs aggregate function. I thought median is an aggregate function.</p>
  
  <p>Below is the code that I am using :-</p>
  
  <pre><code>select * from (
  SELECT DISTINCT ACCOUNT.ACCOUNT_KEY,
     SUM(ACCT_CURR_AMOUNT) OVER (PARTITION BY trunc(execution_local_date_time,'mm'), ACCOUNT.ACCOUNT_KEY,GROUP_TRANSACTION_TYPE_CD ORDER BY trunc(execution_local_date_time,'mm')) AS SUM_OVER_MONTH,
     COUNT(account.ACCOUNT_KEY ) OVER (PARTITION BY trunc(execution_local_date_time,'mm'), ACCOUNT.ACCOUNT_KEY,GROUP_TRANSACTION_TYPE_CD ORDER BY trunc(execution_local_date_time,'mm')) AS COUNT_OVER_MONTH,
     GROUP_TRANSACTION_TYPE_CD                   
  FROM CDS_SCHEMA.V_ACCOUNT ACCOUNT 
  LEFT JOIN V_TRANSACTIONS TRA ON TRA.ACCOUNT_KEY = ACCOUNT.ACCOUNT_KEY and EXECUTION_LOCAL_DATE_TIME BETWEEN '01-JAN-2019' and '30-JUN-2019'
  JOIN CDS_SCHEMA.SAM_TRANS_CODE_TYPE_REL TRANS_CODE ON TRA.TRANSACTION_CODE_CD = TRANS_CODE.TRANSACTION_CODE_CD
  JOIN CDS_SCHEMA.SAM_TRANS_TYPE_GROUP_REL TRANS_CODE_GRP ON TRANS_CODE_GRP.TRANSACTION_TYPE_CD = TRANS_CODE.TRANSACTION_TYPE_CD AND  TRANS_CODE_GRP.GROUP_TRANSACTION_TYPE_CD IN('EFT-INN','EFT-OUT','IFT-INN','IFT-OUT','CCE-INN','CCE-OUT','ALL-INN','ALL-OUT')
  where ACCOUNT.ACCOUNT_KEY = '0004447887C1CB00293D55CD3ED1D7B6CB3FE71C'
  )a 
  PIVOT 
  (
      median(SUM_OVER_MONTH) *as* med_val, median(COUNT_OVER_MONTH) as med_vol
      FOR GROUP_TRANSACTION_TYPE_CD
      IN('EFT-INN','EFT-OUT','IFT-INN','IFT-OUT','CCE-INN','CCE-OUT','ALL-INN','ALL-OUT')
  )
  </code></pre>
  
  <p>In the pivot block I keep getting error on "as". Kindly help me find median in pivot</p>