小白求助SQL中的合计怎么写。。

[w345135861]

这个表中的合计改怎么写，下面自己弄了下求大神们帮忙修改下：

SELECT  
case when grouping(FSupplyID)=1  then '合计' else FSupplyID end as 客户名称,
FNumber as 物料代码,
Fmodel as 规格型号,
FItemID as 物料名称,
sum(Fqty)  as 销售数量,
sum(FConsignAmount) as 销售金额
from vwICBill_8 
where FSupplyID='天猫'
Group By FSupplyID,FNumber,Fmodel,FItemID with rollup 

[二月十六]

如果只是想要这个效果，并且数据只汇总最后所有的，直接这样写就行了

DECLARE @tab1 TABLE
    (
      FSupplyID NVARCHAR(100) ,
      FNumber NVARCHAR(100) ,
      Fqty INT ,
      FConsignAmount INT
    )    
INSERT @tab1 SELECT '天猫','PI900',40,800    
UNION ALL SELECT '天猫','PI900',60,2400  
UNION ALL SELECT '天猫','PI900',30,1500   
UNION ALL SELECT '天猫','PI900',100,6000   
UNION ALL SELECT '天猫','PI900',50,25000 
UNION ALL SELECT '天猫','PI900',80,48000   

--汇总显示    
SELECT  *
FROM    @tab1
UNION ALL
SELECT  '合计' AS FSupplyID ,
        NULL AS FNumber ,
        SUM(Fqty) AS Fqty ,
        SUM(FConsignAmount) AS FConsignAmount
FROM    @tab1
GROUP BY FSupplyID
        
		

结果：



如果中间还需要有什么别的汇总统计，看看这个
http://blog.csdn.net/helloword4217/article/details/12793297

[goodnumber111]

这个用union all比较容易实现吧, WITH Q as(XXX) select * FROM Q union all select 合计,null,null,sum(X) from Q group by XX 我刚学只会这个,其他方法大神解答,顺便说下这么写是不是效率很低

[道素]

参考一下

;WITH tb(ID,col,n1,n2,n3)AS(
   SELECT '100','a',1,1,1 UNION ALL
   SELECT '100','a',10,11,12 UNION ALL
   SELECT '100','b',100,200,300 UNION ALL
   SELECT '100','b',50,60,70 
)
SELECT ISNULL(ID,N'合计'),ISNULL(col,'') AS col,SUM(n1) AS n1,SUM(n2) AS n2,SUM(n3) AS n3 
FROM tb 
GROUP BY ID,col WITH ROLLUP

HAVING GROUPING(ID)=GROUPING(col)

     col  n1          n2          n3
---- ---- ----------- ----------- -----------
100  a    11          12          13
100  b    150         260         370
??        161         272         383

[道素]

上面的例子，GROUPING(ID)=GROUPING(col)是为了剔除小计，如果group by 字段超过两个

;WITH tb(ID,col1,col2,n1,n2,n3)AS(
   SELECT CONVERT(NVARCHAR,'100'),'a','c',1,1,1 UNION ALL
   SELECT '100','a','c',10,11,12 UNION ALL
   SELECT '100','b','c',100,200,300 UNION ALL
   SELECT '100','b','a',50,60,70 
)
SELECT ISNULL(ID,N'合计') AS ID,ISNULL(col1,'') AS col1,ISNULL(col2,'') AS col2,SUM(n1) AS n1,SUM(n2) AS n2,SUM(n3) AS n3 
FROM tb 
GROUP BY ID,col1,col2 WITH ROLLUP
HAVING GROUPING(ID)=GROUPING(col1) AND GROUPING(ID)=GROUPING(col2)

ID                             col1 col2 n1          n2          n3
------------------------------ ---- ---- ----------- ----------- -----------
100                            a    c    11          12          13
100                            b    a    50          60          70
100                            b    c    100         200         300
合计                                       161         272         383