title: SQL Server 2008 Arithmetic overflow error converting expression to data type int
body: <p>I'm having an issue in SQL Server I've been trying to debug for quite some time now and I can't figure out where the problem is.</p>
  
  <p>If I run this query…</p>
  
  <pre><code>SELECT 
      CAST(Hotel_Id AS bigint) AS Hotel_Id, 
      Hotel_Name, 
      CAST(Room_Category AS bigint) AS Room_Category, 
      Room_Category_Name, 
      CAST(Room_Type AS bigint) AS Room_Type, 
      Room_Type_Name, 
      Info_URL_Title, 
      Info_URL , 
      MAX(CAST(ISNULL(Price_Excl_VAT, 0) AS bigint)) AS Price_Excl_VAT, 
      CASE 
          WHEN MAX(CAST(ISNULL(Price_Excl_VAT, 0) AS bigint)) = 0 
          THEN 0 ELSE MAX(CAST(ISNULL(Price, 0) AS bigint)) - MAX(CAST(ISNULL(Price_Excl_VAT, 0) AS bigint)) 
      END AS VAT,
      MAX(CAST(ISNULL(Price, 0) AS bigint)) AS Price,
      MAX(CAST(ISNULL(Dep_Amount, 0) AS [bigint])) AS Dep_Amount  
  FROM 
      uvw_HotelAllotmentToBook 
  WHERE Client_Id = 'CLIENT' AND Project_Id = 'PROJECT' 
      AND Allotment_Date &gt;= '2014-05-11' AND Allotment_Date &lt; '2014-05-14' 
  GROUP BY Hotel_Id, Hotel_Name, Room_Category, Room_Category_Name, Room_Type, Room_Type_Name, Info_URL_Title, Info_URL, Dep_Amount
  HAVING COUNT(Allotment_Date) &gt;= 3
  </code></pre>
  
  <p>In for example one project that we have a bit over 4000 records it keeps giving me this error</p>
  
  <pre><code>Msg 8115, Level 16, State 2, Line 1
  Arithmetic overflow error converting expression to data type int.
  </code></pre>
  
  <p>I've been googling around but can't find a way to fix this, I tried as you can see casting all number fields to big int with no luck.</p>
  
  <p>I need to find the MAX of the prices because the price per night can change.</p>
  
  <p>UPDATE</p>
  
  <pre><code>SELECT Hotel_Id
     ,Hotel_Name
     ,Room_Category AS Room_Category
     ,Room_Category_Name
     ,Room_Type AS Room_Type
     ,Room_Type_Name
     ,Info_URL_Title
     ,Info_URL
     ,COUNT(Allotment_Date)
     ,MAX(CAST(ISNULL(Price_Excl_VAT, 0) AS BIGINT)) AS Price_Excl_VAT
     ,CASE WHEN MAX(CAST(ISNULL(Price_Excl_VAT, 0) AS BIGINT)) = 0 THEN 0
           ELSE MAX(CAST(ISNULL(Price, 0) AS BIGINT)) - MAX(CAST(ISNULL(Price_Excl_VAT, 0) AS BIGINT))
      END AS VAT
     ,MAX(CAST(ISNULL(Price, 0) AS BIGINT)) AS Price
     ,MAX(CAST(ISNULL(Dep_Amount, 0) AS BIGINT)) AS Dep_Amount
  FROM uvw_HotelAllotmentToBook
  WHERE Client_Id = 'PCWHK'
      AND Project_Id = 'INTA14'
      AND Allotment_Date &gt;= '2014-05-11'
      AND Allotment_Date &lt; '2014-05-14'
  GROUP BY Hotel_Id
     ,Hotel_Name
     ,Room_Category
     ,Room_Category_Name
     ,Room_Type
     ,Room_Type_Name
     ,Info_URL_Title
     ,Info_URL
     --,Dep_Amount
  HAVING COUNT(Allotment_Date) &gt;= 3
  </code></pre>
  
  <p>I keep getting the same the overflow error but the moment I comment out this line</p>
  
  <pre><code>   ,MAX(CAST(ISNULL(Dep_Amount, 0) AS BIGINT)) AS Dep_Amount
  </code></pre>
  
  <p>The error is gone.</p>
  
  <p>The problem is that I need the Dep_Amount in the result.</p>
  
  <p>Any help would be very much appreciated.</p>