title: Use OVER (PARTITION BY ) instead of Group By
body: <p>right now I am using temp table in my sql query but I want to use Partition By function instead.</p>
  
  <p>My temp table query is given below:</p>
  
  <pre><code>drop table #Temp;
  
  create table #Temp
  (
    NAME varchar(50),
    EMPID varchar(50),
    SS MONEY,
    PP MONEY
  );
  
  insert into #Temp
   select * From
   (  
    select
     p1.NAME,
     p1.EMPID,
     case when p1.AmtPayer = 'SELF' then sum(p1.Salary) else 0 end as S,
     case when p1.AmtPayer = 'MANAGER' then sum(p1.Salary) else 0 end as P
    from Candidate p1 
    group by p1.Name, p1.EMPID, p1.AmtPayer
   ) as P;
  
  select 
   t.NAME,
   t.EMPID,
   sum(t.SS) as 'SELF PAID',
   sum(t.PP) as 'PARTY PAID' 
  from #Temp t
  group by t.NAME, t.EMPID;
  </code></pre>
  
  <p>I am getting the expected result as well but I want to perform this operation using Partition function , I tried for it but result is not accurate -</p>
  
  <pre><code>select
   NAME,
   EMPID,
   sum(Salary) over (partition by AmtPayer) as Total
  from dbo.Candidate 
  </code></pre>
  
  <p>Output is: </p>
  
  <pre>
  Vivek   0001    300.00
  Vivek   0001    300.00
  Vivek   0001    6200.00
  Vivek   0001    6200.00
  Vivek   0001    6200.00
  </pre>
  
  <p>But I need:</p>
  
  <pre>
  Vivek   0001    6200.00 300.00
  </pre>