Use OVER (PARTITION BY ) instead of Group By

Question

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

Please indent your code so that it shows up as code and format it with some degree of sanity... it's really hard to read this. — John Chrysostom, Jan 07 '14 at 12:49
If you want the result grouped you need to use group by. The whole purpose of window functions is not **not** "reduce" the result set. — , Jan 07 '14 at 12:51
possible duplicate of [Combine multiple results in a subquery into a single comma-separated value](http://stackoverflow.com/questions/111341/combine-multiple-results-in-a-subquery-into-a-single-comma-separated-value) — , Jan 07 '14 at 12:52
In any case, you don't need a temporary table. Lookup something about the `with` statement, which allows you to do a group by over a group by (which will also solve your problem). — Luaan, Jan 07 '14 at 12:57

score 9 · Answer 1 · edited Jan 07 '14 at 13:20

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 :)

score 0 · Answer 2 · edited Jan 07 '14 at 13:58

I used below query:

DROP TABLE #Temp
CREATE TABLE #Temp(
 NAME VARCHAR(50),
 EMPID VARCHAR(50),
 SS MONEY,
 PP MONEY
)
INSERT INTO #Temp
Select * From(
SELECT DISTINCT
  NAME,EMPID, 
  SUM(CASE WHEN AmtPayer='SELF' then Salary ELSE 0 end) OVER (PARTITION BY AmtPayer) AS SS ,
  SUM(CASE WHEN AmtPayer='MANAGER' THEN  Salary ELSE 0 end) OVER (PARTITION BY AmtPayer) AS PP
FROM dbo.Candidate 
)AS P
SELECT DISTINCT t.NAME ,t.EMPID ,SUM(t.SS) OVER(PARTITION BY t.NAME,t.EMPID) AS 'SELF PAID',
SUM(t.PP)  OVER(PARTITION BY t.NAME,t.EMPID) AS 'PARTY PAID' FROM #Temp t
--GROUP BY t.NAME ,t.EMPID

Use OVER (PARTITION BY ) instead of Group By

2 Answers2