title: How to use SUM in pivot table(CAST cannot be used in aggregate function)
body: <p>I am using the following pivot query:</p>
  
  <pre><code>    select UserId_Fk,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday
        from CheckInCheckOut
  
      PIVOT
      (
  
       SUM(CAST(CountHours as decimal(18,2))) 
       FOR Day
       IN([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday])
  
      )
     as PivotTable
  
     Table[CheckInCheckOut]     
  
   CheckInCheckOutId  int 
      UserId_Fk       int 
    CountHours    nvarchar(50)    
           Day    nvarchar(50)    
  </code></pre>
  
  <p>There is something wrong how I am using SUM here. I tried </p>
  
  <pre><code>   select SUM(CAST(CountHours as decimal(18,2))) from CheckInCheckOut
  </code></pre>
  
  <p>which gives me the correct result, but the same I am not able to use in Pivot table.</p>