title: Sum of 2 rows using CASE Statement
body: <p>I would like to display the SUM of <strong>Subscriptional</strong> row and showed as one <strong>Subscriptional</strong>. My script showed below:</p>
  
  <p><img src="https://i.stack.imgur.com/iSK7J.png" alt="enter image description here"></p>
  
  <pre><code>DECLARE @DATENOW datetime
  SET @DATENOW = Convert(DateTime, DATEDIFF(DAY, 0, GETDATE()))
  use ews
  Select CASE
      WHEN ot.PaymentType=0 
      THEN 'Transactional - '
      ELSE 'Subscriptional - '
      End as 'Payment Type',
            count (distinct o.OrderNumber) as 'Count' from    [order] AS o WITH (NOLOCK) LEFT OUTER JOIN
        OrderProducts AS op WITH (NOLOCK) ON o.OrderNumber = op.OrderNumber LEFT OUTER JOIN
        OrderTransaction AS ot WITH (NOLOCK) ON o.OrderNumber = ot.OrderNumber
      where  o.Datecreated &gt;=DATEADD(D,-1,@DATENOW ) 
      AND o.Datecreated &lt;=DATEADD(ms,-3,@DATENOW )
      AND o.status not like'%Deleted%'
      group  by ot.PaymentType
      UNION
       SELECT 'Deleted', count(*)
       from   [order] AS o WITH (NOLOCK) LEFT OUTER JOIN
                      OrderProducts AS op WITH (NOLOCK) ON o.OrderNumber = op.OrderNumber LEFT OUTER JOIN
                      OrderTransaction AS ot WITH (NOLOCK) ON o.OrderNumber = ot.OrderNumber
                     where  o.Datecreated &gt;=DATEADD(D,-1,@DATENOW ) 
      AND o.Datecreated &lt;=DATEADD(ms,-3,@DATENOW )
      and o.status like'%Deleted%'
       group  by ot.paymenttype
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/IF7lk.png" alt="enter image description here"></p>
  
  <p>Here's the raw result, it showed that the Transactional STD and WB summed up on the 2nd result, but the Subscriptional SCR and SSR didn't. I'm looking to summed both Subscriptional SSR and SCR on the 2nd result to. </p>
  
  <p><em>i.e</em><br>
  Subscriptional - 6<br>
  Transactional - 26</p>