title: sql server running total with over, partition
body: <p>I am trying to calculate running total in following query</p>
  
  <pre><code>select 
      a.ICode, MONTH(a.VDate), YEAR(a.vdate)
      , sum(isnull(a.qty, 0))
      , sum(isnull(a.qty, 0)) OVER (partition by a.icode order by a.icode) AS 'total' 
  from 
      t_Stock as a
  group by 
      a.ICode, MONTH(a.VDate), YEAR(a.vdate)
  order by 
      a.icode, YEAR(a.vdate), MONTH(a.VDate)
  </code></pre>
  
  <p>but I am getting an error:</p>
  
  <blockquote>
    <p>Msg 8120, Level 16, State 1, Line 3<br>
    Column 't_Stock.Qty' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</p>
  </blockquote>
  
  <p>Why does '<code>t_Stock.Qty</code>' needs to be in <code>GROUP BY</code> clause as I am already using aggregate function <code>(Sum(a.Qty))</code> ?</p>
  
  <p>Some demo data : </p>
  
  <pre><code>icode   vtype   qty   vdate 
  32114   Sales   -2    2013-06-03 18:09:17.953 
  33459   Sales   -1    2013-06-03 19:39:59.843 
  34446   Sales   -1    2013-06-03 20:46:17.030 
  39914   Tra     -3    2014-01-07 13:02:31.000
  30899   Sales   -1    2013-06-04 11:48:06.267 
  25676   Sales   -3    2013-06-04 17:34:01.470 
  32126   Sales   -1    2013-06-04 18:12:44.267 
  34688   Sales   -1    2013-06-04 18:40:52.750 
  31550   Sales   -1    2013-06-04 19:26:40.937 
  32795   Sales   -1    2013-06-05 12:03:00.250 
  </code></pre>