Use OVER (PARTITION BY ) instead of Group By
sql-server, window-functions
Solution
To do exactly what you want, try this:
select
Name, EmpId,
sum(case when AmtPayer = 'SELF' then Salary else 0 end) as [Self],
sum(case when AmtPayer = 'MANAGER' then Salary else 0 end) as [Manager]
from dbo.Candidate
group by Name, EmpId;
You can use case statements in aggregate functions, which enables you to do a lot of crazy stuff :)
However, as noted in my comments to your question, this is only useful if you have a fixed number of AmtPayer variants that you know in advance.
To elaborate more: `partition by` is explicitly designed not to reduce the result set. It will still return one row per row, and there's nothing you can do to change that - if you do want to reduce the result set, you use `group by` instead. Combined with all the complex stuff you can do with aggregate functions, this is actually a very powerful tool - and that applies to both `partition by` and `group by`. Also note that `partition by` can be much slower than `group by`. In fact, I found out that using partition by to get result count (ie. `count over (partition by NULL)` or something similar) is much slower than simply doing two queries, one just for the count, and the other for the actual results.
Don't assume your way is better because it looks smarter - always measure. Profiling is your friend. Systems like SQL Server are doing a lot of optimizations that try all the time to give you great performance for seemingly stupid queries :)
Problem
right now I am using temp table in my sql query but I want to use Partition By function instead. My temp table query is given below: ``` 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; ``` 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 - ``` select NAME, EMPID, sum(Salary) over (partition by AmtPayer) as Total from dbo.Candidate ``` Output is: ``` Vivek 0001 300.00 Vivek 0001 300.00 Vivek 0001 6200.00 Vivek 0001 6200.00 Vivek 0001 6200.00 ``` But I need: ``` Vivek 0001 6200.00 300.00 ```