CREATE TABLE [test](

[id] [int] IDENTITY(1,1) NOT NULL,
[type] [varchar](50) NULL,
[name] [varchar](50) NULL,
[oldPrice] [float] NULL,
[newPrice] [float] NULL
) ON [PRIMARY]
 
INSERT INTO [test]([type],[name],[oldPrice],[newPrice]) VALUES('电视','电视1',800,700)
INSERT INTO [test]([type],[name],[oldPrice],[newPrice]) VALUES('电视','电视2',1000,900)
INSERT INTO [test]([type],[name],[oldPrice],[newPrice]) VALUES('计算机','计算机1',3600,3200)
INSERT INTO [test]([type],[name],[oldPrice],[newPrice]) VALUES('计算机','计算机2',4000,3800)
INSERT INTO [test]([type],[name],[oldPrice],[newPrice]) VALUES('手机','手机1',2000,1500)
INSERT INTO [test]([type],[name],[oldPrice],[newPrice]) VALUES('手机','手机2',3000,2800)
 
select isnull([type],'总计') [type],SUM(oldPrice) oldSum,SUM(newPrice) newSum
,AVG(oldPrice) oldAvg,AVG(newPrice) newAvg
,ISNULL(cast(cast(round(sum(newPrice)*1.0/sum(oldPrice),4)*100 as decimal(10,2)) as varchar(200))+'%',0) as PriceReductinRate
from test
group by [type]
with rollup