title: select case with "over partition by"
body: <p>What's the correct syntax or is it possible to use case in a select and in it partition by? (using sql server 2012)</p>
  
  <pre><code>a = unique id
  b = a string'xf%'
  c = values
  d = values 
  e = values
  
  
  
  select 
      case 
      when b like 'xf%' then
      (sum(c*e)/100*3423 over (partition by a))end as sumProduct
  from #myTable
  </code></pre>
  
  <p>this is something i need to solve which is a part of a problem i had 
  previously <a href="https://stackoverflow.com/questions/32522130/sumproduct-in-sql">sumProduct in sql</a></p>
  
  <p><strong>edit</strong>: upon request adding some sample data and expected result
  create table #testing (b varchar (20), a date, c int, e int)</p>
  
  <pre><code>     b           a           c         e       sumProduct (expected)
      xf1m    2015.03.02       1         3       (1*3 + 2*5 + 4*2 +3*6)*100/3423
      xf3m    2015.03.02       2         5       (1*3 + 2*5 + 4*2 +3*6)/100*3423
      xf5y    2015.03.02       4         2       (1*3 + 2*5 + 4*2 +3*6)/100*3423
      xf10y   2015.03.02       3         6       (1*3 + 2*5 + 4*2 +3*6)/100*3423
      adfe    2015.03.02       2         5    ---this is skipped because is not xf%
      xf1m    2013.02.01       7         2        (7*2 + 1*8 + 10*1)/100*3423
      xf15y   2013.02.01       1         8        (7*2 + 1*8 + 10*1)/100*3423
      xf20y   2013.02.01       10        1        (7*2 + 1*8 + 10*1)/100*3423
  </code></pre>
  
  <p>I saw that the problem is this: stuff are being added up even if they don't correspond the criteria. In my fiddle you can see that the result for sumProduct is 49 instead of 39 because 2*5 from adfe is being added. What can I do about that? </p>
  
  <pre><code>create table #testing (b varchar (20), a date, c int, e int)
  
  insert into #testing (b,a,c,e)
  values
  ('xf1m','2015-03-02','1','3'),
  ('xf3m','2015-03-02','2','5'),
  ('xf5y','2015-03-02','4','2'),
  ('xf10y','2015-03-02','3','6'),
  ('adfe','2015-03-02','2','5'),
  ('xf1m','2013-02-01','7','2'),
  ('xf15y','2013-02-01','1','8'),
  ('xf20y','2013-02-01','10','1')
  </code></pre>
  
  <p><strong>edit: found the solution, wrote it as an answer below</strong></p>