title: How do you sum non integer values in SQL Server?
body: <p>Since the <code>SUM</code> function only returns <code>INT</code>, what function can you use to sum non-integer values like float, decimal or money?</p>
  
  <p>I've seen some claims that the return type of SUM adjusts based on the data types you give it but that is not the case. It only returns <code>INT</code>. I've given it <code>FLOAT</code>, <code>DECIMAL</code> and <code>MONEY</code>, all return <code>INT</code>.</p>
  
  <p>Here's an example query:
  PayAmount is type <code>MONEY</code>, FXRate is type <code>FLOAT</code></p>
  
  <pre><code>SELECT EmployeeID,
         SUM(PayAmount * FXRate) AS AmountInUSD
  FROM EmployeeTransactions
  GROUP BY EmployeeID
  </code></pre>
  
  <p>Or with conversion:</p>
  
  <pre><code>SELECT EmployeeID,
         SUM(CONVERT(DECIMAL(18,6), PayAmount * FXRate)) AS AmountInUSD
  FROM EmployeeTransactions
  GROUP BY EmployeeID
  </code></pre>
  
  <p>Still fails.</p>
  
  <p>Example with constants:</p>
  
  <pre><code>SELECT EmployeeID,
         SUM(3.14159 * 2.365) AS AmountInUSD
  FROM EmployeeTransactions
  GROUP BY EmployeeID
  </code></pre>
  
  <p>Result should be 7.42986035. SQL Server returns 7.</p>