title: Cognos: Count the number of occurences of a distinct id
body: <p>I'm making a report in Cognos Report Studio and I'm having abit of trouble getting a count taht I need. What I need to do is count the number of IDs for a department. But I need to split the count between initiated and completed. If an ID occures more than once, it is to be counted as completed. The others, of course, will be initiated. So I'm trying to count the number of ID occurences for a distinct ID. Here is the query I've made in SQl Developer:</p>
  
  <pre><code>SELECT
  COUNT((CASE WHEN COUNT(S.RFP_ID) &gt; 8 THEN MAX(CT.GCT_STATUS_HISTORY_CLOSE_DT) END)) AS "Sales Admin Completed"
  ,COUNT((CASE WHEN COUNT(S.RFP_ID) = 8 THEN MIN(CT.GCT_STATUS_HISTORY_OPEN_DT) END)) as "Sales Admin Initiated"
  FROM 
  ADM.B_RFP_WC_COVERAGE_DIM S
  JOIN ADM.B_GROUP_CHANGE_REQUEST_DIM CR 
  ON S. RFP_ID = CR.GCR_RFP_ID 
  JOIN ADM.GROUP_CHANGE_TASK_FACT CT 
  ON CR.GROUP_CHANGE_REQUEST_KEY = CT.GROUP_CHANGE_REQUEST_KEY 
  JOIN ADM.B_DEPARTMENT_DIM D 
  ON D.DEPARTMENT_KEY = CT.DEPARTMENT_RESP_KEY
  WHERE CR.GCR_CHANGE_TYPE_ID = '20'
  AND S.RFP_LOB_IND = 'WC'
  AND S.RFP_AUDIT_IND = 'N'
  AND CR.GCR_RECEIVED_DT BETWEEN '01-JAN-13' AND '31-DEC-13'
  AND D.DEPARTMENT_DESC = 'Sales'
  AND CT.GCT_STATUS_IND = 'C' 
  GROUP BY S.RFP_ID ;
  </code></pre>
  
  <p>Now this works. But I'm not sure how to translate taht into Cognos. I tried doing a CASE taht looked liek this(this code is using basic names such as dept instead of D.DEPARTMENT_DESC):</p>
  
  <pre><code> CASE WHEN dept = 'Sales' AND count(ID for {DISTINCT ID}) &gt; 1 THEN count(distinct ID)END)
  </code></pre>
  
  <p>I'm using count(distinct ID) instead of count(maximum(close_date)). But the results would be the same anyway. The "AND" is where I think its being lost. It obviously isn't the proper way to count occurences. But I'm hoping I'm close. Is there a way to do this with a CASE? Or at all?</p>
  
  <p>--EDIT--
  To make my question more clear, here is an example:</p>
  
  <p>Say I have this data in my table</p>
  
  <pre><code>ID
  ---
  1
  2
  3
  4
  2
  5
  5
  6
  2
  </code></pre>
  
  <p>My desired count output would be:</p>
  
  <pre><code>Initiated   Completed
  ---------   ---------
      4           2
  </code></pre>
  
  <p>This is because two of the distinct IDs (2 and 5) occure more than once. So they are counted as Completed. The ones that occure only once are counted as Initiated. I am able to do this in SQl Dev, but I can't figure out how to do this in Cognos Report Studio. I hope this helps to better explaine my issue.</p>