title: How to find values that appear more than twice per day in a UNION ALL select in Db2
body: <p>I need to check the account number which came in more than 2 times in any one day.</p>
  
  <p>Below is my query, I'm getting the results where accounts number which came in more than 2 only from FAILURE not from SUCCESS. I need to get from both. Any helps are appreciated.</p>
  
  <p>Sample data:</p>
  
  <pre><code>04/28/2020 123345 BERLIN 5645756768 SUCCESS
  04/28/2020 123346 BERLIN 5645756768 FAILURE
  04/28/2020 123344 BERLIN 5645756768 SUCCESS
  04/28/2020 123344 BERLIN 5645756761 FAULTED
  04/28/2020 123345 BERLIN 5645756763 FAILURE
  04/28/2020 123346 BERLIN 5645756764 SUCCESS
  04/28/2020 123347 BERLIN 5645756766 FAILURE
  04/28/2020 123344 BERLIN 5645756763 TIME OUT
  04/28/2020 123344 BERLIN 5645756760 PENDING
  
  Query:
  
  SELECT
      DATE,
      ID,
      NAME,
      ACCOUNT,
      STATUS
  FROM
  (
  SELECT
      T1.DATE AS DATE, 
      T1.ID AS ID,
      T1.NAME AS NAME,
      T2.ACCOUNT_NUMBER AS ACCOUNT,
      T2.STATUS AS STATUS,
      COUNT(1) OVER (PARTITION BY T2.ACCOUNT_NUMBER, DATE(DATE) CNT
  FROM TABLE1 AS T1
  JOIN TABLE2 AS T2
  ON T2.ID = T1.ID 
  WHERE STATUS = 'SUCCESS'
  GROUP BY T1.ID, T1.NAME,T2.ACCOUNT_NUMBER,T2.STATUS
  
  UNION ALL
  
  SELECT
      T1.DATE AS DATE,
      T1.ID AS ID,
      T1.NAME AS NAME,
      T2.ACCOUNT_NUMBER AS ACCOUNT,
      T2.STATUS AS STATUS,
      COUNT(1) OVER (PARTITION BY T2.ACCOUNT_NUMBER, DATE(DATE) CNT
  FROM TABLE1 AS T1
  JOIN TABLE2 AS T2
  ON T2.ID = T1.ID 
  WHERE STATUS = 'FAILED'
  GROUP BY T1.ID, T1.NAME,T2.ACCOUNT_NUMBER,T2.STATUS
  )
  WHERE CNT &gt; 2;'
  </code></pre>
  
  <p>Expected Output:</p>
  
  <pre><code>04/28/2020 123345 BERLIN 5645756768 SUCCESS
  04/28/2020 123346 BERLIN 5645756768 FAILURE
  04/28/2020 123344 BERLIN 5645756768 SUCCESS
  </code></pre>