SQLTeam.com | Weblogs | Forums

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.*,   
cast(Lead(e.Value ) 
over
(
partition by e.DeptNo, e.LocationID, e.Pid, e.Sid order by e.DeptNo, e.LocationID, e.Pid, e.Sid
 ) as varchar(50)) +'/' +cast( e.Value  as varchar(10)) as Div,

 cast( Lead(e.Value ) over(partition by e.DeptNo, e.LocationID, e.Pid, e.Sid order by e.DeptNo, e.LocationID, e.Pid, e.Sid ) /e.Value as decimal(5,1)) as Result
 
 from #empTbl as e