使用GROUPING添加合计结果

--创建表

CREATE TABLE SALE(FCP NVARCHAR(30),FQTY DECIMAL(9,2),FPRICE DECIMAL(10,2),FAMOUNT AS FQTY*FPRICE)

--填充演示数据 

INSERT INTO SALE(FCP,FQTY,FPRICE) SELECT '电脑',2,3000
INSERT INTO SALE(FCP,FQTY,FPRICE) SELECT '鼠标',3,50
INSERT INTO SALE(FCP,FQTY,FPRICE) SELECT '键盘',5,80
INSERT INTO SALE(FCP,FQTY,FPRICE) SELECT '内存条',1,150.5

--在组后追加一条小计行
SELECT CASE WHEN GROUPING(FCP)=1 THEN '小计' ELSE FCP END AS FNAME , SUM(FQTY) AS FQTY
,SUM(FAMOUNT) AS FAMOUNT, GROUPING(FCP)AS AMOUNT_Grouping, GROUPING(FCP) AS 'QTY_Grouping'
FROM SALE

GROUP BY FCP WITH ROLLUP