title: SQL Rollup to Sum Totals of a Grouped Table
body: <p>I have a table that is grouped by <em>'Group Type'</em>.  What I need is for the last row to display the sum of the values in the above rows in the <em>Total</em> row.  </p>
  
  <p>Below is the code currently but I am not sure how to sum multiple fields at once.  </p>
  
  <p><strong>Actual</strong></p>
  
  <p><a href="https://i.stack.imgur.com/L1DOG.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/L1DOG.png" alt="ROLLUP-1"></a></p>
  
  <p><strong>Expected Result</strong></p>
  
  <p><a href="https://i.stack.imgur.com/CU6rL.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/CU6rL.png" alt="enter image description here"></a></p>
  
  <p>Do I need to add another CASE statement to 
  </p>
  
  <pre class="lang-or-tag-here prettyprint-override"><code>select
  CASE when GROUP_TYPE is null then 'Total' ELSE GROUP_TYPE END as 'Group Type',    
  Sum_Amount, TotalER, [Discount Report]
      from  
      (select GROUP_TYPE, sum(cast(AMOUNT as float)) as Sum_Amount FROM [dbo].[a] 
          where cast(ACTIVITY_DATE as date) between @startdate and @enddate
               group by GROUP_TYPE with rollup) a
  left join
      (select [Charge Group] , sum(cast([Earned Revenue] as float)) as 
      TotalER, sum(cast(Discount as float)) as 'Discount Report'
          from [dbo].[er] group by [Charge Group]) er
  on
      a.GROUP_TYPE = er.[Charge Group]
  
  select sum(cast([er] as float)) from [dbo].[er]
  </code></pre>