sql - 选择不同的案例计数
问题描述
我是 SQL 新手,并试图返回数据集中的一列,仅当另一列满足条件时才对 1 列的总数进行计数和/或求和
我的 Select 子句中有以下内容,但是当我稍后在查询中对数据进行分组并且不输出 G.[Constituent ID] 我无法计算该行(并且不想因为它不会对数据进行分组)我想要的方式)。
有没有解决的办法?
(我正在使用 Microsoft SQL)
SELECT
(Case WHEN G.[Constituent ID] IN ('2014', '2015', '2016', '2017', '2018') THEN COUNT(DISTINCT G.[Gift Import ID])) AS [COUNT of Gifts Anonymous]
这是我的全部陈述
SELECT
a.Apl_Appeal_ID AS [Appeal ID],
A.Apl_Description AS [Appeal Description],
A.Apl_Start_date AS [Appeal Start Date],
A.Apl_Campaign_ID AS [Campaign ID],
SUM(A.Apl_No_solicited) AS [Number Solicited],
SUM(A.Apl_ApE_1_01_Amount) AS [Appeal Expenses],
RIGHT(A.Apl_Appeal_ID,1) AS [PackCode],
A.Apl_AtrCat_1_01_Description AS [Gift Classification],
A.Apl_AtrCat_2_01_Description AS [Channel],
A.Apl_AtrCat_3_01_Description AS [MD/Corp],
A.Apl_AtrCat_4_01_Description AS [Purpose],
A.Apl_AtrCat_5_01_Description AS [Campaign],
A.Apl_AtrCat_6_01_Description AS [Wave],
A.Apl_AtrCat_7_01_Description AS [Supplier],
A.Apl_AtrCat_8_01_Description AS [Timing],
A.Apl_AtrCat_9_01_Description AS [Year],
COUNT(DISTINCT G.[Constituent ID]) AS [COUNT of Donors],
COUNT(DISTINCT G.[Gift Import ID]) AS [COUNT of Gifts],
SUM(G.[Gift Amount]) AS [Income],
(Case WHEN G.[Constituent ID] IN ('2014', '2015', '2016', '2017', '2018') THEN COUNT(DISTINCT G.[Gift Import ID])) AS [COUNT of Gifts Anonymous]
FROM dbo.[FHF_Appeals] A
LEFT OUTER JOIN dbo.FHF_Gifts_ExcludingRG G
ON A.Apl_Appeal_ID = G.[Appeal List]
WHERE (a.Apl_Campaign_ID = '1002100-Acquisition' AND A.Apl_AtrCat_2_01_Description LIKE 'Direct_Mail')
AND A.Apl_Start_date >= '1/1/2014'
GROUP BY
A.Apl_Appeal_ID,
A.Apl_Description,
A.Apl_Start_date,
A.Apl_Campaign_ID,
A.Apl_AtrCat_1_01_Description,
A.Apl_AtrCat_2_01_Description,
A.Apl_AtrCat_3_01_Description,
A.Apl_AtrCat_4_01_Description,
A.Apl_AtrCat_5_01_Description,
A.Apl_AtrCat_6_01_Description,
A.Apl_AtrCat_7_01_Description,
A.Apl_AtrCat_8_01_Description,
A.Apl_AtrCat_9_01_Description
解决方案
我认为您希望将CASE
表达式作为以下参数的参数COUNT(DISTINCT)
:
COUNT(DISTINCT CASE WHEN G.[Constituent ID] IN ('2014', '2015', '2016', '2017', '2018')
THEN G.[Gift Import ID]
END) AS [COUNT of Gifts Anonymous]
推荐阅读
- nfs - mkdir 在 NFS4 上从一个客户端传播到另一个客户端的速度有多快?
- max - 最大值和最小值没有给出真实的最大值和最小值
- asynchronous - 在 F# 中处理对 C# 库的异步调用列表
- c++ - std::vector 没有重载函数的实例与参数列表匹配
- python - Pyspark 在读取目录中的所有 parquet 文件时失败,但在单独处理文件时成功
- python - Numpy 不一起广播兼容的形状
- java - 两次单击以执行操作?
- javascript - 更改数组项会创建其自身的无限嵌套副本?
- firebase - 来自 Firestore 的数据未显示在列表视图中
- laravel - Laravel 模型在运行播种机后不会刷新其 ID