SQL-汇总案例/何时来自不同字段的值 [英] SQL- Summing Case/When values from different fields
问题描述
我正在尝试对从 CASE/WHEN 语句生成的值求和.唯一的问题是这些值来自不同的字段.
I am trying to SUM values generated from a CASE/WHEN statement. The only problem is that the values come from different fields.
示例:
这是我的查询中许多项中的一组开销值.在此示例中,我需要将开销值 14.850000 和 1.166600 相加到 16.016.
vac_type 列确定成本的类型.在这种情况下,当 vac_type = WO1 或 WO2 时,它们是间接成本.W10 不是,因此是 NULL 值.
Example:
This is a set of overhead values from one item out of many in my query. In this example, I need to SUM the overhead values 14.850000 and 1.166600 to 16.016.
The vac_type column determines the type of costs. In this case, when vac_type = WO1 or WO2, they are overhead costs. W10 is not, thus the NULL value.
总共有 12 个 vac_types 是开销值.它们都列在我的 CASE/WHEN 语句中.我需要按 item_no 对所有这些开销值求和,但由于 vac_type 字段有两个不同的值,我的查询认为它们彼此不同,无法求和.
In all, there are 12 vac_types that are overhead values. They are all listed in my CASE/WHEN statement. I need to SUM all of these overhead values by item_no, but since the vac_type field has two different values, my query considers them DISTINCT from each other and unable to be summed.
这是我尝试通过 item_no 对开销求和的失败尝试.它生成上面截图来自的列表:
This is my unsuccessful attempt to SUM Overhead by item_no. It generates the list in which the screenshot above comes from:
SELECT DISTINCT 'Overhead' = SUM(CASE
WHEN vac_type IN('A01', 'AD0', 'ADX', 'A01', 'CI0', 'DO1', 'DP9',
'O20', 'PWO', 'TO1', 'WO1', 'WO2')
THEN (Cost_Values)
ELSE NULL
END,
item_no,
comp_item,
vac_type
FROM table_1
GROUP BY item_no,
comp_item,
vac_type
推荐答案
感谢@Lamak 的回答.为了按 item_no 求和开销,需要从 SELECT 和 GROUP BY 语句中删除任何唯一字段.通过删除 vac_type,我得到了我需要的数据.
Thank you to @Lamak for answering. In order to SUM overhead by item_no, any unique fields need to be removed from the SELECT and GROUP BY statement. By removing vac_type, I got the data I needed.
SELECT 'Overhead' = Sum(CASE
WHEN vac_type IN ( 'A01', 'AD0', 'ADX', 'AO1',
'CI0', 'DO1', 'DP9', 'O20',
'PWO', 'TO1', 'WO1', 'WO2' )
THEN (Cost_Values)
ELSE NULL
END),
item_no,
comp_item_no
FROM table_1
GROUP BY item_no,
comp_item
这篇关于SQL-汇总案例/何时来自不同字段的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!