## 4 replies

Last post Apr 27, 2017 06:36 AM by asp.ambur

• asp.ambur

Member

399 Points

1101 Posts

### Group Wise Total On Result Set

Hello

 Code ItemName Qty Meter B1 A 5 2 B1 Z 10 3 B1 W 5 5 B2 J 2 3 B2 W 6 4 B2 W 2 1 B3 W 1 2 B3 K 2 2 B3 L 2 2

I want like this below Code Wise Total

 Code ItemName Qty Meter B1 A 5 2 B1 Z 10 3 B1 W 5 5 Total 20 10 B2 J 2 3 B2 W 6 4 B2 W 2 1 Total 10 8 B3 W 1 2 B3 K 2 2 B3 L 2 2 Total 5 6
• RichardY

Contributor

6864 Points

1980 Posts

### Re: Group Wise Total On Result Set

Typically you would use the ROLLUP function.  See this for specific examples:

• asp.ambur

Member

399 Points

1101 Posts

### Re: Group Wise Total On Result Set

Will you pls give me the code with example
• Chris Zhao

All-Star

17612 Points

3510 Posts

### Re: Group Wise Total On Result Set

Apr 27, 2017 06:11 AM|Chris Zhao|LINK

Hi Asp.ambur,

declare @tb table
(Code nvarchar(10),ItemName nvarchar(10),Qty int,Meter int)

insert into @tb values
('B1','A',5,2),
('B1','Z',10,3),
('B1','W',5,5),
('B2','J',2,3),
('B2','W',6,4),
('B2','W',2,1),
('B3','W',1,2),
('B3','K',2,2),
('B3','L',2,2)

;with t1 as
(
select Code,ItemName,Qty,Meter,Code as name
from @tb
),
t2 as
(
select 'Total' as code,'' as itemname,sum(Qty) as Qty,sum(Meter) as Meter,Code as name
from @tb
group by code
),
t3 as
(
select * from t1
union all
select * from t2
)

select Code,ItemName,Qty,Meter from t3
order by name,Qty

Best Regards,

Chris

• asp.ambur

Member

399 Points

1101 Posts