使用group by对sql server中的行值进行求和 [英] Sum row values in sql server with group by

问题描述

Hi Frnds，

我有一张桌子，上面有以下详细信息，

 EmpID CashID CashName奖励理由
 
 1 1旅行1000班加罗尔旅行
 1 1旅行2000钦奈旅行
 1 2食物500 6月月
 1 2食物900 7月月
 2 1旅行5000钦奈
 2 2食品3000德里
 1 3住宿8000金奈
 2 3住宿2000德里
 2 3住宿1000德里
 

我需要根据Empid和CashId组合行...我需要输出如下

 EmpID CashId CashName奖励
 1 1旅行3000 
 1 2食物1400 
 1 3住宿8000 

我尝试过分组和总和。

解决方案

试试这个：

  SELECT  EmpID，CashID，CashName，SUM（奖励） AS  SumOfIncentives 
  FROM  YourTableName 
  GROUP   BY  EmpID，CashID，CashName 

总和的示例：

  DECLARE   @ tmp  表（A  INT ，B  INT ，C  VARCHAR （ 10 ），D  INT ）
 
  INSERT   INTO   @ tmp （A，B，C，D）
  SELECT   1   AS  A， 2   AS  B，'  A'  AS  C， 2   AS  D 
  UNION   ALL  
  SELECT   1   AS  A， 2   AS  B，'  A'  AS  C， 3   A. S  D 
  UNION  所有 
  SELECT   2   AS  A， 3   AS  B，'  B'   AS  C， 4   AS  D 
  UNION   ALL  
  SELECT   2   AS  A， 3   AS  B，'  B'  AS  C， 5   AS  D 
 
  SELECT  A，B，C，SUM（D）< span class =code-keyword> AS  SumOfD 
  FROM   @ tmp  
  GROUP   BY  A，B，C 
  UNION   ALL  
  SELECT   0   AS  A， 0   AS  B，' 总计：'  AS  C，SUM（D） AS  SumOfD 
  FROM   @ TMP  

Hi Frnds,
I have a Table With following details,

EmpID          CashID     CashName      Incentives       Reason

  1               1         Travel        1000        Bangalore trip
  1               1         Travel        2000        Chennai trip
  1               2         Food           500        June Month
  1               2         Food           900        July Month
  2               1         Travel        5000        Chennai
  2               2         Food          3000        Delhi
  1               3         Staying       8000        Chennai
  2               3         Staying       2000        Delhi
  2               3         Staying       1000        Delhi

I need to Combine rows as per Empid and CashId...I need Output as follows

EmpID        CashId         CashName       Incentives
1             1              Travel          3000
1             2              Food            1400    
1             3              Staying         8000

I have tried with group by and sum.

解决方案

Try this:

SELECT EmpID, CashID, CashName, SUM(Incentives) AS SumOfIncentives
FROM YourTableName
GROUP BY EmpID, CashID, CashName

Example for total SUM:

DECLARE @tmp TABLE (A INT, B INT, C VARCHAR(10), D INT)

INSERT INTO @tmp (A, B, C, D)
SELECT 1 AS A, 2 AS B, 'A' AS C, 2 AS D
UNION ALL
SELECT 1 AS A, 2 AS B, 'A' AS C, 3 AS D
UNION ALL
SELECT 2 AS A, 3 AS B, 'B' AS C, 4 AS D
UNION ALL
SELECT 2 AS A, 3 AS B, 'B' AS C, 5 AS D

SELECT A, B, C, SUM(D) AS SumOfD
FROM @tmp
GROUP BY A,B,C
UNION ALL
SELECT 0 AS A, 0 AS B, 'Total: ' AS C, SUM(D) AS SumOfD
FROM @tmp

这篇关于使用group by对sql server中的行值进行求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！