# Sql query based on Group by function with different scenario

#1

Hi All,

I have table like below but i need to calculate divison of two columns based on group by deptno,locationid,pid,sid.But here i have different empids.I need output mentioned below.Here empids are staticand those will not change.For particular combination of group by columns will get only 2 empids or some time may be will get only 1.so in that case anything by null or zero should be zero.

TableName:

Need sql qury for below output:In my calculation im considering group by deptno,locationid,pid,sid.But i need to take empid 2 value divided by empid 1(453/200=2.2)value for corresponding group by columns and for some another different group set combination i need to take empid 4 value divided byempid 3 value f(147/741=0.19)for division formula calculation.Below i just given result value as just for forumula purpose.

Output:
Deptno locationid pid sid Result
30 20 10 50 453/200
30 20 40 60 147/741

Any one can guide on sql query for the above output..

#2

This might get you started:

``````with cte
as (select empid
,deptno
,locationid
,pid
,sid
,cast([value] as decimal) as [value]
,row_number() over(partition by deptno
,locationid
,pid
,sid
order by empid
)
as rn
from yourtable
)
select a.deptno
,a.locationid
,a.pid
,a.sid
,case
when a.[value]=0
then 0
else isnull(b.[value],0)
/a.[value]
end as [value]
from cte as a
left outer join cte as b
on b.deptno=a.deptno
and b.locationid=a.locationid
and b.pid=a.pid
and b.sid=a.sid
and b.rn=2
where a.rn=1
;
``````

#3

Hi Bitsmed,

Actually i have provided data as tabke in the example,i have getiing those column values from different tables so i have written multiple inner conditions as per my requiremnt and finally i got one data based on my query so i written one more select statement on that table that is mentioned below.Assume table name testing is below is script for retrieving data as per my requirement.Can you help me how i can apply yours code mentioned above to the below script.

the below cript i have used to retrieve my table data.
select Empid,Deptno,locationid,pid,sid,
sum(value)
from testing
group by
deptno, locationid,pid,sid;

#4

That would be something like:

``````with cte
as (select empid
,deptno
,locationid
,pid
,sid
,cast(sum([value]) as decimal) as [value]
,row_number() over(partition by deptno
,locationid
,pid
,sid
order by empid
)
as rn
from testing
group by empid
,deptno
,locationid
,pid
,sid
)
select a.deptno
,a.locationid
,a.pid
,a.sid
,case
when a.[value]=0
then 0
else isnull(b.[value],0)
/a.[value]
end as [value]
from cte as a
left outer join cte as b
on b.deptno=a.deptno
and b.locationid=a.locationid
and b.pid=a.pid
and b.sid=a.sid
and b.rn=2
where a.rn=1
;
``````

#5

another way

``````create table #empTbl
(
Empid int,
DeptNo int,
LocationId int,
Pid int,
Sid int,
Value int

)

insert into #empTbl
values
(1,30,20,10,50,200),
(2,30,20,10,50,453),
(3,30,20,40,60,741),
(4,30,20,40,60,147);

select * from #empTbl

select  e.*,