title: sumProduct in sql
body: <p>I'm trying implementing sumproduct (from excel) in my table on the server.</p>
  
  <pre><code>select * 
  into #myTable2
  from #myTable1
  
  select
  a, 
  b,
  c,
  d,
  e,
  (
  select (c * e)/100*3423) from #myTable1 t1
  inner join #myTable t2
  on t1.b = t2.b
  where b like 'axr%'
  ) as sumProduct
  from #myTable1
  </code></pre>
  
  <p>but this doesn't quite work. Can't spot the error, maybe i'm just tired or missing it. </p>
  
  <p><strong>edit:</strong>
  sample data and desired results</p>
  
  <p>will mention only the important columns</p>
  
  <pre><code>c     e    b        a                             sumProduct      
  2     4   axr1     2012.03.01                     2*4 + 3*8 
  3     8   axr3     2012.03.01                     2*4 + 3*8 
  7     5   axr23    2011.01.01                     7*5 + 3*2
  3     2   axr34    2011.01.01                     7*5 + 3*2
  </code></pre>
  
  <p><strong>EDIT2</strong>:
  I need some help with the syntax. I'm trying to rewrite this part: </p>
  
  <pre><code>select (c * e)/100*3423) from #myTable1 t1
      inner join #myTable t2
      on t1.b = t2.b
      where b like 'axr%'
      ) as sumProduct
      from #myTable1
  </code></pre>
  
  <p>as</p>
  
  <pre><code>case
  when t.b like 'axr%' then
  (sum(t.c * t.e) /100*3234) end as sumProduct from #myTable t
  </code></pre>
  
  <p>Can't get the syntax right, but should work like that</p>
  
  <p><strong>edit 3:</strong>
  <strong>got it to work</strong> like this:</p>
  
  <pre><code>case
  when b like 'axr%' then
  (sum(c*e)/100*3423)end as sumProduct
  </code></pre>
  
  <p>and at the end of the code</p>
  
  <pre><code>group by  --had an error without this
  a,b,c,d,e 
  </code></pre>
  
  <p>How could i do this for every date (let's say the date is the column 'a' or whatever name). <strong>How can I incorporate</strong> <code>over (partition by a)</code> in the code above?</p>
  
  <p>want something like </p>
  
  <pre><code>case 
  when b like 'axr%' then
  (sum(c*e)/100*3423 over (partition by a))end as sumProduct
  </code></pre>