The tables information is like :
In oracle , we can use SUM(distinct xxx) over partition by
select e.empno,
e.ename,
SUM(distinct e.sal) over (partition by e.deptno) as total_sal,
e.deptno,
SUM(e.sal * case when eb.type =1 then .1
when eb.type =2 then .2
else .3 end )
over (partition by deptno) as total_bonus
from emp e, emp_bonus eb
where e.empno=eb.empno and
e.deptno=10
order by 1
But, it is impossible to use SUM( distinct xx) over partition by in sqlserver. It needs some adjust ,
Sqlserver:
select X.empno,X.ename,SUM(X.total_sal) over(partition by X.deptno)
,X.total_bonus
from(
select e.empno,
e.ename,
e.deptno,
case when
ROW_NUMBER() over (partition by e.deptno,e.ename order by e.deptno)=1
then SUM(e.sal)
end as total_sal,
SUM(e.sal*case when eb.type=1 then .1
when eb.type=2 then .2
else .3 end) over (partition by deptno) as total_bonus
from emp e, emp_bonus eb
where e.empno=eb.empno and e.deptno=10
group by e.empno,e.ename,e.sal,e.deptno,eb.type
) X
where X.total_sal is not null
group by X.empno,X.ename,X.deptno,X.total_sal,X.total_bonus
Yes, use that way , not only achieve the goal , but also eliminated duplicate data.
Attention please:
Why the last value of total_sal is NULL ?
Yes, you got it. In sqlserver, ROW_NUMBER() over partition by just could only pick up
the first row from result.
Thanks
Comments