title: Using field in CASE without group by
body: <p>I have the following code and I am having trouble figuring out how to NOT include these into the group by. Some of the arguments are purely for the case and that is all. I can't include them in the group by. I cam't really group by anything else as I need to get the counts by TransactionTyp only but I keep getting this error: is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. </p>
  
  <p>Maybe the use of CASE is the wrong way to go about this? But I need to replace some of the values based on other fields.</p>
  
  <p>This is in MS SQL too.</p>
  
  <pre><code>SELECT  Count(*), 
      CASE 
          WHEN a.TransactionTyp IS NOT NULL THEN a.TransactionTyp
          WHEN a.ClaimStatus = 'Resolved-Deflected' THEN 'Deflected'
          WHEN a.ClaimStatus = 'Resolved-NoAction' THEN 'Deflected'
          WHEN a.ClaimStatus = 'Open' AND b.AssignOperator = 'PendClaim.NF_POS_Pinless' THEN 'Affiliate'
      END As TransactionTyp
  FROM Table1 a
  LEFT JOIN Table2 b
      ON a.ClaimNbr = b.ClaimDisputeNbr AND b.CreateDte = Convert(varchar,GetDate(), 101)
  WHERE a.ClaimEntryDte = Convert(varchar,GetDate(),101)    
  AND a.ClaimTypCd  IN ('DEBS', 'DEBSI', 'DBCIN', 'DBCUS')
  GROUP BY TransactionTyp
  </code></pre>