SUM of values generates incorrect totals
<p>I'm writing a report to total a specific set of transactions grouped by day however, the total is incorrect.  In the code listed below, no errors are generated but the daily total is off by 11.  </p>

<pre><code>SELECT
'1,*'+char(13)+char(10)
+'80,1006062'+char(13)+char(10)
+'100,10'+char(13)+char(10)
+'2405,'+cast(sum(trans.QUANTITY) as varchar(18))+char(13)+char(10) --Census events --as varchar(10)
+'9999,'+cast(count(distinct trans.TX_ID) as varchar(18))+char(13)+char(10) --Count of Records (for analysis/ validation only)
+'2420,'+format(trans.SERVICE_DATE,'M/d/yyyy') --as service date of Observation Procedure
FROM PAT_ENC_HSP hsp
inner join HSP_TRANSACTIONS trans on hsp.HSP_ACCOUNT_ID=trans.HSP_ACCOUNT_ID
WHERE TRANS.TX_TYPE_HA_C = '1'  AND-- Billed procedures
  datediff(day,trans.SERVICE_DATE,cast(CURRENT_TIMESTAMP as date)) between 7 and 7 AND
  PROC_ID in ('90068','94788','94790','94792','94794','10240') 

group by format(trans.SERVICE_DATE,'M/d/yyyy')
order by format(trans.SERVICE_DATE,'M/d/yyyy')
</code></pre>

<p>This generates the results...</p>

<pre><code>1,*80,1006062  100,10  2405,305.000  9999,90  2420,4/25/2016
</code></pre>

<p>I double checked my totals by exporting the results of this query into Excel, which also generated 90 records for 25 Apr.  However, the total quantity was "294" and not "305"</p>

<pre><code>SELECT DISTINCT TRANS.TX_TYPE_HA_C, TRANS.PROCEDURE_DESC, TRANS.PROC_ID, TRANS.DEPARTMENT, TRANS.QUANTITY, TRANS.SERVICE_DATE, TRANS.TX_ID, TRANS.TX_POST_DATE
FROM PAT_ENC_HSP HSP LEFT OUTER JOIN HSP_TRANSACTIONS TRANS on 
HSP.HSP_ACCOUNT_ID=TRANS.HSP_ACCOUNT_ID
WHERE PROC_ID in ('90068','94788','94790','94792','94794','10240')
  AND TRANS.TX_TYPE_HA_C = '1'
  AND TRANS.SERVICE_DATE ={ts '2016-04-25 00:00:00'} 
ORDER BY TRANS.TX_ID
</code></pre>

<p>Results are attached <a href="https://i.stack.imgur.com/z5N10.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/z5N10.png" alt="enter image description here"></a></p>

<p>Not knowing which of these was correct, I recreated the same query in Crystal Reports and once again received the 294 value.  Unfortunately, I have to use the format from the initial query to upload the formatted results into another application.  I'm not sure why the total values are not the same across all three methods and am assuming that I'm doing something wrong in the "cast(sum" statement.  </p>