title: How to use subquery in sql server 2008?
body: <p>I know this question might be repeated ... But I have query like this, I have a field "Compute_CRM_State" in my table. And that field contains data like "approved,pending,cancelled" but now i want to fetch total count of approved or pending or cancelled. I tried it with sub query. I can able to get total count of approved or pending or cancelled but.. I got same records many times. </p>
  
  <p><img src="https://i.stack.imgur.com/Q0Y42.png" alt="result what I am getting from my query."> </p>
  
  <p>query :</p>
  
  <pre><code>SELECT        (SELECT        COUNT(Compute_CRM_State) AS Expr1
                            FROM            CRM_Doctor_Request
                            WHERE        (CRM_State_Id = 1)) AS PENDING,
                               (SELECT        COUNT(Compute_CRM_State) AS Expr2
                                 FROM            CRM_Doctor_Request AS CRM_Doctor_Request_3
                                 WHERE        (CRM_State_Id = 2)) AS Approved,
                               (SELECT        COUNT(Compute_CRM_State) AS Expr3
                                 FROM            CRM_Doctor_Request AS CRM_Doctor_Request_2
                                 WHERE        (CRM_State_Id = 3)) AS CANCELLED
  FROM            CRM_Doctor_Request AS CRM_Doctor_Request_1
  </code></pre>
  
  <p>There should be display only 1 record of count.. but it is getting display the number of times that number of records in table contains. </p>