title: SQL Count Distinct with multiple condition, to return all columns, all records
body: <p>Good Day, I'm trying to count the number of distinct values given a condition but, I need the query result to include all columns, and records. Basically, the distinct count result will be added as a new column to the original table and will just be repeated for each row.</p>
  
  <p>This is what I have so far and I can't get to the next step. I have three tables joined together.  I want to count the unique T.CostCenter in tbl_trantype, given that R.ID (from tbl_reqtype) and R.DocumentNo (from tbl_reqtype) is the same.</p>
  
  <pre><code>SELECT M.[ID] AS EMLID
          ,R.[ID] AS RequestID
          ,T.[ID] AS TransactionID
          ,R.[DocumentNo]
          ,T.[CostCenter]
          ,ROW_NUMBER() OVER (PARTITION BY R.ID, R.DocumentNo, T.CostCenter ORDER BY T.ID) NoOfCostCenter 
  FROM (tbl_Info M
          INNER JOIN tbl_reqtype R ON M.ID = R.EMLID)
          INNER JOIN tbl_trantype T ON R.ID = T.RID
  </code></pre>
  
  <p>Below is the result I'm hoping</p>
  
  <pre>
  EMLID   RequestID   TransactionID   DocumentNo  CostCenter  NoOfCostCenter
  4000001    11           1           777777777   1515151         2
  4000001    11           2           777777777   1515151         2
  4000001    11           3           777777777   4515151         2
  4000003    12           4           999999999   7277477         5
  4000003    12           5           999999999   7277477         5
  4000003    12           6           999999999   8722724         5
  4000003    12           7           999999999   7272726         5
  4000003    12           8           999999999   2652627         5
  4000003    12           9           999999999   6353636         5
  </pre>