我需要使用condtion来汇总SQL表字段 [英] I need to sum SQL table fields using where condtion

问题描述

我的桌子价值是

团体金额
 A组I $ 1500 
 A组II 2000 
 B组I 2500 
 B组II 500 

预期产量

团体金额
 A组I 3500 
 B组I 3000 

怎么办这个？



我尝试了什么：



我正在使用

 选择 组，Sum（Amount）
  From  tblAmount 
  Group   by   Group  

解决方案

由于您正在汇总（汇总）金额，因此您不应将其包含在 GROUP BY 子句中。

  SELECT  [组]，
 SUM（[Amount]） AS  SumAmount 
  FROM  [dbo]。[tblAmount] 
  WHERE  [组]  LIKE  ' ％Group I' 
  GROUP   BY  [组 ] 

BTW，根据您的预期输出，没有涉及 WHERE 类

你想要实现的是按字符串的一部分分组

 SELECT [Group]，SUM（[Amount]）AS SumAmount 
 FROM [dbo]。[tblAmount] 
 GROUP BY LEFT（[Group]，CHARINDEX（'Group'，[小组] +5）

注意，以下声明：

 LEFT（ [集团]，CHARINDEX（'集团'，[ Group] +5）

在[ Group <中搜索字符串 Group / code>]列并返回：{ A Group ， B Group }

My table value is

Group    Amount
A Group I  1500
A Group II 2000
B Group I  2500
B Group II 500

Expected Output

Group     Amount
A Group I    3500
B Group I    3000

How to do this?

What I have tried:

Im using

Select Group,Sum(Amount)
From tblAmount
Group by Group

解决方案

Since you're aggregating (summing) the Amount, you shouldn't include it in the GROUP BY clause.

SELECT   [Group], 
         SUM([Amount]) AS SumAmount 
FROM     [dbo].[tblAmount] 
WHERE [Group] LIKE '%Group I'
GROUP BY [Group]

BTW, there is no WHERE class involved based on your expected output

What you're trying to achieve is called "group by part of string"

SELECT   [Group], SUM([Amount]) AS SumAmount 
FROM     [dbo].[tblAmount] 
GROUP BY LEFT([Group], CHARINDEX('Group', [Group]+5)

Note, that below statement:

LEFT([Group], CHARINDEX('Group', [Group]+5)

searches for string "Group" in a [Group] column and returns: {"A Group", "B Group"}

这篇关于我需要使用condtion来汇总SQL表字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！