hi ,

very goood morning to all

i have a table like below, which is  contain area, zone, branch wise value 

in that i want to get company total as a column and area total as a another column and zone total in next column

AREA	ZONE	BRANCH	VALUE
TN	CENTRAL	AMB	100
TN	CENTRAL	TNF	100
TN	CENTRAL	SDGD	100
TN	CENTRAL	ERW	100
TN	NORTH	AMB	50
TN	SOUTH	AMB	50
TN	WEST	AMB	60
AP	AAA	AMB	120
AP	BBB	AMB	140
AP	CCC	AMB	180
AP	DDD	AMB	200

my expected output is like below

AREA	ZONE	BRANCH	VAL  comtot  areatot  zonetot
TN	CENTRAL	AMB	100	1200	560	400
TN	CENTRAL	TNF	100	1200	560	400
TN	CENTRAL	SDGD	100	1200	560	400
TN	CENTRAL	ERW	100	1200	560	400
TN	NORTH	AMB	50	1200	560	50
TN	SOUTH	AMB	50	1200	560	50
TN	WEST	AMB	60	1200	560	60
AP	AAA	AMB	120	1200	640	120
AP	BBB	AMB	140	1200	640	140
AP	CCC	AMB	180	1200	640	180
AP	DDD	AMB	200	1200	640	200

i acheived this by using query but its will affect performance because my real time scenario dealing with laks of records

select a.*,b.company_tot,c.area_tot,d.zone_tot from FESIBILITY_CHECK  a cross join (select SUM(value)company_tot from FESIBILITY_CHECK )b 
join (select SUM(value)area_tot,area from FESIBILITY_CHECK group by area)c on a.AREA = c.area
join (select SUM(value)zone_tot,area,zone from FESIBILITY_CHECK group by area,zone)d on a.AREA=d.area and a.ZONE = d.ZONE

so i planned to use cube and roll up but i don't get desired result 

thanks in advance, 

give a quick response as soon as possible.

HI,

i tried below query 

select a.*,case when a.AREA  IS null and a.ZONE IS null and a.BRANCH IS null then v end as company_tot ,
case when a.AREA  IS NOT NULL  and a.ZONE IS null and a.BRANCH IS null then v end as area_tot ,
case when a.AREA  IS NOT null and a.ZONE IS NOT null and a.BRANCH IS null then v end as zone_tot ,
ROW_NUMBER () OVER (ORDER BY AREA,ZONE,BRANCH  DESC)ROW
from (
select AREA,ZONE,BRANCH,SUM(value)v from FESIBILITY_CHECK 
group by AREA,ZONE,BRANCH
with rollup)a

the result is like below 

AREA	ZONE	BRANCH	v	company_tot	area_tot	zone_tot	ROW
NULL	NULL	NULL	2270	2270	NULL	NULL	1
AP	NULL	NULL	640	NULL	640	NULL	2
AP	AAA	AMB	120	NULL	NULL	NULL	3
AP	AAA	NULL	120	NULL	NULL	120	4
AP	BBB	AMB	140	NULL	NULL	NULL	5
AP	BBB	NULL	140	NULL	NULL	140	6
AP	CCC	AMB	180	NULL	NULL	NULL	7
AP	CCC	NULL	180	NULL	NULL	180	8
AP	DDD	AMB	200	NULL	NULL	NULL	9
AP	DDD	NULL	200	NULL	NULL	200	10
TN	NULL	NULL	1630	NULL	1630	NULL	11
TN	CENTRAL	TNF	100	NULL	NULL	NULL	12
TN	CENTRAL	SDGD	100	NULL	NULL	NULL	13
TN	CENTRAL	ERW	100	NULL	NULL	NULL	14
TN	CENTRAL	AMB	100	NULL	NULL	NULL	15
TN	CENTRAL	NULL	400	NULL	NULL	400	16
TN	NORTH	AMB	10	NULL	NULL	NULL	17
TN	NORTH	NULL	10	NULL	NULL	10	18
TN	SOUTH	AMB	1200	NULL	NULL	NULL	19
TN	SOUTH	NULL	1200	NULL	NULL	1200	20
TN	WEST	AMB	20	NULL	NULL	NULL	21
TN	WEST	NULL	20	NULL	NULL	20	22

in that result i want to replace next null value with previous not null value so that finally i can remove null rows to get desired result. please check this also and reply me.

Hi,

Can you post sample data and the table schema properly because the data that you have  given does not give a proper information.

Regards,

hi Milan ,

declare @FESIBILITY_CHECK  table(
AREA varchar(10),
	ZONE varchar(10),	BRANCH varchar(10),	v int
)


insert into @FESIBILITY_CHECK select 'TN',	'CENTRAL	','AMB',	100
insert into @FESIBILITY_CHECK select 'TN',	'NORTH	','AMB',	10
insert into @FESIBILITY_CHECK select 'TN',	'SOUTH	','AMB',	1200
insert into @FESIBILITY_CHECK select 'TN',	'WEST	','AMB',	20
insert into @FESIBILITY_CHECK  select'AP',	'AAA	','AMB',	120
insert into @FESIBILITY_CHECK select 'AP',	'BBB	','AMB',	140
insert into @FESIBILITY_CHECK select 'AP',	'CCC	','AMB',	180
insert into @FESIBILITY_CHECK select 'AP',	'DDD	','AMB',	200
insert into @FESIBILITY_CHECK  select 'TN',	'CENTRAL	','TNF',	100
insert into @FESIBILITY_CHECK select 'TN',	'CENTRAL	','SDGD',	100
insert into @FESIBILITY_CHECK select 'TN',	'CENTRAL	','ERW',	100 
select * from @FESIBILITY_CHECK

1. i want to display over all sum as company total in one column

2.in area total 'tn' total and 'ap' total should display in next column

3. in zone total 'central'..... total should display in another one column

hi milan , 

check this also,

declare @FESIBILITY_CHECK  table(
AREA varchar(10),
	ZONE varchar(10),	BRANCH varchar(10),	v int
)


insert into @FESIBILITY_CHECK select 'TN',	'CENTRAL	','AMB',	100
insert into @FESIBILITY_CHECK select 'TN',	'NORTH	','AMB',	10
insert into @FESIBILITY_CHECK select 'TN',	'SOUTH	','AMB',	1200
insert into @FESIBILITY_CHECK select 'TN',	'WEST	','AMB',	20
insert into @FESIBILITY_CHECK  select'AP',	'AAA	','AMB',	120
insert into @FESIBILITY_CHECK select 'AP',	'BBB	','AMB',	140
insert into @FESIBILITY_CHECK select 'AP',	'CCC	','AMB',	180
insert into @FESIBILITY_CHECK select 'AP',	'DDD	','AMB',	200
insert into @FESIBILITY_CHECK  select 'TN',	'CENTRAL	','TNF',	100
insert into @FESIBILITY_CHECK select 'TN',	'CENTRAL	','SDGD',	100
insert into @FESIBILITY_CHECK select 'TN',	'CENTRAL	','ERW',	100 
--select * from @FESIBILITY_CHECK


select a.*,case when a.AREA  IS null and a.ZONE IS null and a.BRANCH IS null then v end as company_tot ,
case when a.AREA  IS NOT NULL  and a.ZONE IS null and a.BRANCH IS null then v end as area_tot ,
case when a.AREA  IS NOT null and a.ZONE IS NOT null and a.BRANCH IS null then v end as zone_tot ,
ROW_NUMBER () OVER (ORDER BY AREA,ZONE,BRANCH  DESC)ROW
from (
select AREA,ZONE,BRANCH,SUM(v)v from @FESIBILITY_CHECK 
group by AREA,ZONE,BRANCH
with rollup)a

here i want to replace null value with previous not null value

Can you check the below query,Also try to create index for area and zone column , which will be increase your query executing time

DECLARE @Total int
SELECT @Total = SUM(v) from @FESIBILITY_CHECK

SELECT AreaTotal.AREA
,AreaZone.ZONE
,BRANCH
,v
,@Total Total
,AreaTotal
,ZoneTotal  FROM 
(
SELECT AREA,SUM(v) AreaTotal from @FESIBILITY_CHECK 
GROUP BY AREA
)
AreaTotal JOIN 
(
SELECT AREA,ZONE,SUM(v) ZoneTotal from @FESIBILITY_CHECK 
GROUP BY AREA,ZONE
)
AreaZone 
ON AreaTotal.AREA = AreaZone.AREA JOIN @FESIBILITY_CHECK CHK
ON CHK.AREA = AreaZone.AREA AND CHK.ZONE = AreaZone.ZONE

• Edited by Loganathan.v 1 hour 31 minutes ago

HAI,

i already mentioned the same query in my first post, it will affect my query performance .

select a.*,b.company_tot,c.area_tot,d.zone_tot from FESIBILITY_CHECK  a cross join (select SUM(value)company_tot from FESIBILITY_CHECK )b 
join (select SUM(value)area_tot,area from FESIBILITY_CHECK group by area)c on a.AREA = c.area
join (select SUM(value)zone_tot,area,zone from FESIBILITY_CHECK group by area,zone)d on a.AREA=d.area and a.ZONE = d.ZONE

declare @FESIBILITY_CHECK  table(
AREA varchar(10),
	ZONE varchar(10),	BRANCH varchar(10),	v int
)


insert into @FESIBILITY_CHECK select 'TN',	'CENTRAL	','AMB',	100
insert into @FESIBILITY_CHECK select 'TN',	'NORTH	','AMB',	10
insert into @FESIBILITY_CHECK select 'TN',	'SOUTH	','AMB',	1200
insert into @FESIBILITY_CHECK select 'TN',	'WEST	','AMB',	20
insert into @FESIBILITY_CHECK  select'AP',	'AAA	','AMB',	120
insert into @FESIBILITY_CHECK select 'AP',	'BBB	','AMB',	140
insert into @FESIBILITY_CHECK select 'AP',	'CCC	','AMB',	180
insert into @FESIBILITY_CHECK select 'AP',	'DDD	','AMB',	200
insert into @FESIBILITY_CHECK  select 'TN',	'CENTRAL	','TNF',	100
insert into @FESIBILITY_CHECK select 'TN',	'CENTRAL	','SDGD',	100
insert into @FESIBILITY_CHECK select 'TN',	'CENTRAL	','ERW',	100 

Select AREA, ZONE, BRANCH, v,
  Sum(v) Over () As comtot,
  Sum(v) Over (Partition By AREA) As areatot,
  Sum(v) Over (Partition By ZONE) As zonetot
From @FESIBILITY_CHECK;

Tom

• Marked as answer by gvrspk veni 1 hour 0 minutes ago

hi tom cooper,

thank you so much its working fine. 

i too expect this kind of effective simple method. 

its too simple and useful.

i tried in hard method like below

--select * from FESIBILITY_CHECK 
;with cte as (
select a.*,case when a.AREA  IN ('a') and a.ZONE IN ('a')and a.BRANCH IN ('a') then v end as company_tot ,
case when a.AREA  not in ('a')  and a.ZONE IN ('a') and a.BRANCH IN ('a') then v end as area_tot ,
case when a.AREA  not in ('a') and a.ZONE not in ('a') and a.BRANCH in ('a') then v end as zone_tot ,
ROW_NUMBER () OVER (ORDER BY AREA,ZONE,branch )ROW
from (
select case when (grouping(AREA)=1) then 'a' else area end as area,
case when (grouping(ZONE)=1) then 'a' else ZONE end as ZONE,
case when (grouping(BRANCH)=1) then 'a' else BRANCH end as BRANCH,
SUM(value)v from FESIBILITY_CHECK 
group by AREA,ZONE,BRANCH
with rollup)a
)
,
mincte as (
select area,zone,BRANCH,company_tot,area_tot,zone_tot,ROW = 1 from cte where ROW = 1 
union all select a.area,a.ZONE,a.BRANCH,ISNULL(a.company_tot,b.company_tot)company_tot
,ISNULL(a.area_tot,b.area_tot)area_tot,
ISNULL(a.zone_tot,b.zone_tot)zone_tot,b.row+1 from cte a inner join mincte b
 on a.row = b.ROW  ) select * from mincte aa
 where aa.AREA not in ('a')  and aa.zone not in ('a')  and aa.BRANCH not in ('a') 

thank you once again