CODEWITHSUNDEEP
sql-serverwindow-functions
user3169167
user3169167

Reputation: 87

Use OVER (PARTITION BY ) instead of Group By

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

Upvotes: 3

Views: 60415

Answers (2)

user3169167
user3169167

Reputation: 87

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

Upvotes: 0

Luaan
Luaan

Reputation: 63732

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

Upvotes: 9

Related Questions