title: How do I total the results of a row group in a column?
body: <p>My table looks something like:</p>
  
  <pre><code> Customer         Error1         Error2         Error3         Error4       TotalErrors      TotalOrders
   CustomerName [SUM(Error1)]   [SUM(Error2)]  [SUM(Error3)] [SUM(Error3) [SUM(TotalErrors)]  [TotalOrders]
   Total
  </code></pre>
  
  <p>The middle row CustomerName is part of a Row Group ordered on CustomerWithErrors which simply tells if a given Customer name has any of the 4 errors. If not, it doesn't show up in the table.</p>
  
  <p>In the last total row, I simply right click and add a total for each column in the row group. This outputs a sum of each column exactly as I want it, except for the last column. When I add a total it gives me the sum of the TotalErrors fields for all customer names, not just the ones with errors (the ones actually shown in the table). Consequently, the number is massive. How can I just get a sum of the numbers displayed in the total errors column?</p>
  
  <p>EDIT: Here are some screenshots of design and preview mode:</p>
  
  <p>Design:  <a href="http://i.imgur.com/6kEmzw3.png" rel="nofollow">http://i.imgur.com/6kEmzw3.png</a></p>
  
  <p>Preview:  <a href="http://i.imgur.com/NOpWmAI.png" rel="nofollow">http://i.imgur.com/NOpWmAI.png</a></p>
  
  <p>EDIT 2: SQL FOR THE TOP TABLIX:</p>
  
  <p>Some of this was new to me so it probably doesn't look very well done. Let me know what I can explain. Thanks.</p>
  
  <pre><code> SELECT 
           PKEY
          ,Customer
          ,isnull(CustomerName, 'Unknown') AS CustomerName
          ,CustomerWithErrors
          ,Warehouse
          ,ErrorMessage
          ,PROCESSDATE
          ,CARTONERRORMESSAGE
          ,Division
          ,BOLNO
          ,BOLSHIPDATE
          ,CARTONDETAILERRMSG
          ,ErrorCategory
          ,CASE WHEN ErrorCategory='Success'
          THEN 0
          ELSE    
          COUNT(NotLate) 
          END AS OnTimeCount
          ,COUNT(Invalid) AS InvalidCount
          ,COUNT(WrongData) AS WrongDataCount
          ,COUNT(Duplicate) AS DuplicateCount
          ,COUNT(MissingData) AS MissingDataCount
          ,(COUNT(Invalid)+COUNT(WrongData)+COUNT(Duplicate)+COUNT(MissingData)) AS TotalErrors
          ,Total945s AS TotalReport945s
          --,COUNT(*) OVER(PARTITION BY [CustomerWithErrors]) Total945s 
          , (SELECT (Total945s) WHERE CustomerWithErrors&gt;=0) AS TotalErrors
  
          FROM(
  
      SELECT
           PKEY
          ,Customer
          ,CASE WHEN CustomerName = '' OR CustomerName IS NULL
          THEN 'Unknown'
          ELSE CustomerName
          END AS CustomerName
          ,CASE WHEN CustomerName='' OR CustomerName IS NULL OR CustomerName='Unknown'
          THEN 0
          ELSE
          (SELECT DISTINCT Customer WHERE ErrorCategory &lt;&gt; 'Success') END
           AS CustomerWithErrors
          ,Warehouse
          ,ErrorMessage
          ,PROCESSDATE
          ,CARTONERRORMESSAGE
          ,Division
          ,BOLNO
          ,BOLSHIPDATE
          ,CARTONDETAILERRMSG
          ,ErrorCategory
          ,CASE WHEN ErrorCategory='Success'
          THEN 'N'
          ELSE OnTime         
          END AS OnTime
          ,(SELECT Customer WHERE OnTime = 'Y') AS NotLate
          ,(SELECT Customer WHERE ErrorCategory = 'Invalid') AS Invalid
          ,(SELECT Customer WHERE ErrorCategory = 'Wrong Data') AS WrongData
          ,(SELECT Customer WHERE ErrorCategory = 'Duplicate') AS Duplicate
          ,(SELECT Customer WHERE ErrorCategory = 'Missing Data') AS MissingData
          , COUNT(*) OVER(PARTITION BY [Customer]) Total945s
  
  
  
      FROM EDI945UTIErrors
  
  
      ) S
  
  
          GROUP BY
           PKEY
          ,Customer
          ,CustomerName
          ,CustomerWithErrors
          ,Warehouse
          ,ErrorMessage
          ,PROCESSDATE
          ,CARTONERRORMESSAGE
          ,Division
          ,BOLNO
          ,BOLSHIPDATE
          ,CARTONDETAILERRMSG
          ,ErrorCategory
          ,OnTime
          ,Total945s
  </code></pre>
  
  <p>EDIT 3: SQL WITH PIVOT:
  Does this look like what I should do with aggregates in the report? Thanks.</p>
  
  <pre><code> SELECT 
           S.PKEY 
          ,Customer
          ,isnull(S.CustomerName, 'Unknown') AS CustomerName
          ,CustomerWithErrors
          ,Warehouse
          ,ErrorMessage
          ,PROCESSDATE
          ,CARTONERRORMESSAGE
          ,Division
          ,BOLNO
          ,BOLSHIPDATE
          ,CARTONDETAILERRMSG
          ,ErrorCategory
          ,CASE WHEN ErrorCategory='Success'
          THEN 0
          ELSE    
          COUNT(NotLate) 
          END AS OnTimeCount
          ,Invalid
          ,WrongData
          ,Duplicate
          ,MissingData
          --,COUNT(Invalid) AS InvalidCount
          --,COUNT(WrongData) AS WrongDataCount
          --,COUNT(Duplicate) AS DuplicateCount
          --,COUNT(MissingData) AS MissingDataCount
          --,(COUNT(Invalid)+COUNT(WrongData)+COUNT(Duplicate)+COUNT(MissingData)) AS TotalErrors
          ,Total945s AS TotalReport945s
  
          --,COUNT(*) OVER(PARTITION BY [CustomerWithErrors]) Total945s 
          --, (SELECT (Total945s) WHERE CustomerWithErrors&gt;=0) AS TotalErrors
  
          FROM(
  
      SELECT
           PKEY 
          ,Customer
          ,CASE WHEN CustomerName = '' OR CustomerName IS NULL
          THEN 'Unknown'
          ELSE CustomerName
          END AS CustomerName
          ,CASE WHEN CustomerName='' OR CustomerName IS NULL OR CustomerName='Unknown'
          THEN 0
          ELSE
          (SELECT DISTINCT Customer WHERE ErrorCategory &lt;&gt; 'Success') END
           AS CustomerWithErrors
          ,Warehouse
          ,ErrorMessage
          ,PROCESSDATE
          ,CARTONERRORMESSAGE
          ,Division
          ,BOLNO
          ,BOLSHIPDATE
          ,CARTONDETAILERRMSG
          ,ErrorCategory
          ,CASE WHEN ErrorCategory='Success'
          THEN 'N'
          ELSE OnTime         
          END AS OnTime
          ,(SELECT Customer WHERE OnTime = 'Y') AS NotLate
          --,(SELECT Customer WHERE ErrorCategory = 'Invalid') AS Invalid
          --,(SELECT Customer WHERE ErrorCategory = 'Wrong Data') AS WrongData
          --,(SELECT Customer WHERE ErrorCategory = 'Duplicate') AS Duplicate
          --,(SELECT Customer WHERE ErrorCategory = 'Missing Data') AS MissingData
          , COUNT(*) OVER(PARTITION BY [Customer]) Total945s
  
  
  
      FROM EDI945UTIErrors
      --WHERE Customer ='104646' and OnTime = 'Y'
      --WHERE CUSTOMER='107834'
      --AND ErrorCategory = 'Wrong Data'
      --AND ERRORMESSAGE LIKE '%Overshipment Not Allowed%'
  
      ) S
      LEFT JOIN
      (
      SELECT PKEY, ISNULL(CustomerName, 'Unknown') AS CustomerName, [Invalid], [WrongData], [Duplicate], [MissingData], [Success]
   FROM EDI945UTIErrors
   PIVOT (Count(Customer) FOR ErrorCategory in ([Invalid], [WrongData], [Duplicate], [MissingData], [Success]))
   AS P) AS PT ON S.PKEY = PT.PKEY
  
          GROUP BY
           S.PKEY
          ,Customer
          ,S.CustomerName
          ,CustomerWithErrors
          ,Warehouse
          ,ErrorMessage
          ,PROCESSDATE
          ,CARTONERRORMESSAGE
          ,Division
          ,BOLNO
          ,BOLSHIPDATE
          ,CARTONDETAILERRMSG
          ,ErrorCategory
          ,OnTime
          ,Total945s
          ,Invalid
          ,WrongData
          ,MissingData
          ,Duplicate
  </code></pre>
  
  <p>Thank you very much for your time.</p>