title: Group by to specific column in SQL Server
body: <p>I have following tables <code>candidatedata</code> and <code>interview_data</code></p>
  
  <p>candidatedata column</p>
  
  <pre><code>candidate_id    |indent_no    |canName             |canMob
  --------------------------------------------------------------
  9               |MIT-301      |AMIT PANDIT SAWANT  |NULL
  112             |MIT-286      |AMIT PANDIT SAWANT  |NULL
  168             |214382       |Amit Srivastava     |8699405324
  227             |213837       |Amit Kumar Nagar    |7508088604
  409             |213468       |Amit Kumar Tripathi |NULL
  679             |MIT-325      |Amit Kumar Joshi    |9900551259
  774             |REQ48394     |Amit Patel          |9110898760
  862             |MIT-370      |Amit Agrawal        |9986502032
  1170            |MIT-388      |Amit Kumar Singh    |7717751688
  1246            |MIT-392      |Amit Sahu           |9131399323
  1498            |MIT-383      |Amit Kumar          |9871020318
  1751            |227106-new   |Amit Singh          |9632134007
  </code></pre>
  
  <p>interview_data</p>
  
  <pre><code>candidate_id    |status1          |off_status    |cvstatus               |rec_name
  ----------------------------------------------------------------------------------
  674             |ReSchedule       |NULL          |NULL                   |Dipali
  587             |CV fbd Pdg       |NULL          |CV Feedback Pending    |Reeta
  637             |CV Reject        |NULL          |CV Reject              |Pallavi
  1539            |CV Reject        |NULL          |CV Reject              |Sonam
  89              |Intv Shortlist   |NULL          |NULL                   |Swathi
  965             |Intv Reject      |NULL          |NULL                   |Pallavi
  480             |Intv Reject      |NULL          |NULL                   |Shishal
  532             |CV fbd Pdg       |joined        |CV Feedback Pending    |Reeta
  977             |CV fbd Pdg       |NULL          |CV Feedback Pending    |Nalini
  1593            |Intv Shortlist   |NULL          |Srilakshmi             |
  1204            |CV Reject        |NULL          |CV Reject              |Sonam
  </code></pre>
  
  <p>I want following output</p>
  
  <pre><code>indent_no,rec_name,count(cv fbd Pdg),count(total),name of joined candidate
  </code></pre>
  
  <p>I have applied group by clause with count but facing problem when adding name of candidate who is join</p>
  
  <p>This is my current query</p>
  
  <pre><code>   SELECT
    indent_no,
    COUNT(CASE WHEN cvstatus IS NOT NULL THEN 1 END) AS Total,
    COUNT(CASE WHEN cvstatus LIKE 'CV Feedback Pending%' THEN 1  END) CVfdbPdg,
    (CASE WHEN off_status LIKE 'Joine%' THEN cd.canName END) as Joined
  FROM interview_data id
  JOIN candidatedata cd  ON id.candidate_id = cd.candidate_id
  GROUP BY indent_no, canName
  </code></pre>