Que:sum all (table:MF) SUM(QUANTITY),SUM(AMT) use group with rollup and having grouping NO DESCRIPTION specification UOM QUANTITY Price AMT 01 A1 1*3 pcs 10 2 20 02 A2 2*3 pcs 15 2 30 03 B1 3*4 pcs 6 3 18 04 B2 4*9 pcs 5 3 15 i Want to group by combined results are as follows,(use group with rollup and grouping): NO DESCRIPTION specification UOM QUANTITY Price AMT 01 A1 1*3 PCS 10 2 20 02 A2 2*3 pcs 15 2 30 03 B1 3*4 pcs 6 3 18 04 B2 4*9 pcs 5 3 15 SUMALL: 36 83 --SQL create table create table mf(NO varchar(5),description varchar(10),specification varchar(10),UOM varchar(5),Quantity int,price int,AMT int) insert into mf select '01','A1','1*3','PCS',10,2,20 union all select '02','A2','2*3','PCS',15,2,30 union all select '03','B1','3*4','PCS',6,3,18 union all select '04','B2','4*9','PCS',5,3,15 asked 15 Dec '13, 08:22 mfkpie8 |
answered 20 Dec '13, 07:28 Reimer Pods Detection of NULLs using the GROUPING function ,But he didn't check how NULL only keep a line
(21 Dec '13, 22:58)
mfkpie8
|
MICROSOFT SQL : select case grouping(NO) when 1 then 'SUMALL:' else NO end 'NO', case grouping(NO) when 1 then '' else description end 'description', case grouping(NO) when 1 then '' else specification end 'specification', case grouping(NO) when 1 then '' else UOM end 'UOM', case grouping(NO) when 1 then sum(quantity) else max(quantity) end 'quantity', case grouping(NO) when 1 then '' else rtrim(price) end 'price', case grouping(NO) when 1 then sum(amt) else max(amt) end 'amt' from mf group by NO,description,specification,UOM,Price with rollup having grouping(NO)=1 or grouping(Price)=0 it's at SQL 2005 by i need use SYbase ASA answered 20 Dec '13, 06:53 mfkpie8 |