How to get Data Group by in Subquery. My query is like below:
ALTER Procedure [C349549_jazeera].[Pro_FRegionBind]
@did int
As
Begin
Declare @Region TABLE([auto] int IDENTITY(1,1) NOT NULL,
RegId int,DeptId int,RegionName varchar(100),CDate DateTime DEFAULT GETDATE(),IsActive char(1) DEFAULT 0)
insert into @Region(RegId,DeptId,RegionName,CDate,IsActive)
select r.RegId,r.DeptId,r.RegionName,r.CDate,r.IsActive from RegionName r where r.RegionName='All'
insert into @Region(RegId,DeptId,RegionName,CDate,IsActive)
select r.RegId,r.DeptId,r.RegionName,r.CDate,r.IsActive from RegionName r where r.RegionName<>'All'
order by r.RegionName Asc
Select rn.RegId,[auto],Case rn.RegionName when 'All' then
rn.RegionName + ' ('+ Convert(varchar(50),(Select Count(*) from Jobdetails jd
inner join DepartmentName dn on dn.DeptId=jd.DeptId where dn.DeptId=jd.DeptId and jd.IsActive=0 and dn.DeptId=@did Group by jd.RegId ))+')'
else
rn.RegionName + ' ('+ Convert(varchar(50),( Select Count(dn.DeptId) from Jobdetails jd inner join RegionName rn
on rn.RegId=jd.RegId inner join DepartmentName dn on dn.DeptId=jd.DeptId
where rn.RegId=jd.RegId and dn.DeptId=jd.DeptId and rn.DeptId=dn.DeptId
and jd.IsActive=0 and jd.DeptId IN(@did)
))+')'
End as RegionName
from @Region rn LEFT OUTER JOIN jobdetails jd
on rn.RegId=jd.RegId inner join DepartmentName dn on dn.DeptId=rn.DeptId
where rn.DeptId=@did and dn.IsActive=0 and rn.IsActive=0 and
rn.RegionName is not null GROUP by rn.RegId,rn.RegionName,[auto]
order by [auto]
End
when i am trying to get data group by in the highlighted subquery then i am getting the following error.
System.Data.SqlClient.SqlException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Please suggest me, how i will get data group by in subquery in my case.
