title: SQL Server - Grouping a field correctly
body: <p>Im running the query</p>
  
  <p>SELECT </p>
  
  <pre><code>    YEAR(TL_SYS_DATE)               AS [YEAR]
      ,MONTH(TL_SYS_DATE)             AS [MONTH]
      ,NEW_PRODUCT_NAME               AS [PRODUCT]
      ,COMP_PDT_CODE                  AS PROD_CODE
      ,NEW_CONTRACT_NO
      ,NEW_STATUS
      ,PREMIUM_AMT
  
      ,COUNT(DISTINCT CASE WHEN TERM_LENGTH = '10 Year' AND NEW_STATUS IN ('Active/Restrict','Active') THEN
              NEW_CONTRACT_NO 
      END)                                            AS [10 YR CONTRACTS]
  
  
      ,SUM(CASE WHEN TERM_LENGTH = '10 Year' AND NEW_STATUS IN ('Active/Restrict','Active') THEN
              PREMIUM_AMT
      END)                                            AS [10 YR Premium]
  
      ,SUM(CASE WHEN NEW_STATUS IN ('Surr/Freelook', 'Terminated/Surr','Withdrawn') THEN
              SURRENDER_VALUE
      END)                                            AS [Surr/Term/Withdrawn Amount]     
  
      ,SUM(CASE WHEN NEW_STATUS NOT IN ('Surr/Freelook', 'Terminated/Surr','Withdrawn') THEN
              ACCOUNT_VALUE
      END)                                            AS [ACCOUNT VALUE]
  
      -- NEW_STATUS NOT IN ('Surr/Freelook', 'Terminated/Surr','Withdrawn') AND TERM_LENGTH != '10 Year'
  
      ,COUNT(DISTINCT CASE WHEN UPPER(NEW_STATUS) = 'CLAIM' THEN
              NEW_CONTRACT_NO 
      END)                                            AS [Death Claim Contracts]
  
  FROM
      DB1
      where YEAR(TL_SYS_DATE) = '2014'
      and MONTH(TL_SYS_DATE) = '4'
      AND PREMIUM_AMT &gt; 0
  GROUP BY
      YEAR(TL_SYS_DATE)
      ,MONTH(TL_SYS_DATE)
      ,NEW_PRODUCT_NAME
      ,ACCOUNT_VALUE      
      ,COMP_PDT_CODE
      ,NEW_CONTRACT_NO
      ,NEW_STATUS
      ,PREMIUM_AMT
  </code></pre>
  
  <p>which is resulting in </p>
  
  <p><a href="https://i.stack.imgur.com/KnBmM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KnBmM.png" alt="Result 1"></a></p>
  
  <p>What i would like to do is sum the PREMIUM_AMT but not the ACCOUNT_VALUE        </p>
  
  <p>So for contract 6880000153 it would be PREMIUM_AMT = 110500.00 AND ACCOUNT VALUE 105306.07 All on one row.</p>