Case When Distinct value then sum another value?(Case When Distinct 值然后求和另一个值?)
问题描述
小猪退缩了我昨天提出的另一个问题.
Piggy backing off another question I had yesterday.
我想知道如何计算 amt > 1500 的不同记录数.根据我的数据连接方式,我可以多次反映相同的 PKey AcctNo,因为我的完整外部连接到另一个具有多个事务记录的表.
I was wondering how I would go about counting the distinct number of records that have an amt > 1500. The way my data is joined, I could have the same PKey AcctNo reflected more than one time because my full outer joined to another table that has multiple transactional records.
(Case When AcctNo_PKey = distinct then sum(case when amount > 1500 then 1 else 0 end)
else 0) end as GT1500
这是我当前的代码,可产生所需的结果.我
this my current code that produces a desired result. I
SELECT sum(case when amount > 1500 then 1 else 0 end) as GT1500
, sum(case when amount < 1500 then 1 else 0 end) as LT1500
, DATEPART(Year, amount.Date) Deposit_Year
, DATEPART(QUARTER, amount.Date) Deposit_Qtr
From account
full outer JOIN amount ON account.AcctNo = amount.AcctNo
group by DATEPART(Year, amount.Date)
, DATEPART(QUARTER, amount.Date)
也许我的整个方法都是错误的...idk
Or maybe my entire approach is wrong...idk
推荐答案
您可以对 CASE
表达式的输出使用 COUNT(DISTINCT )
.例如,要计算具有 [amount]
[amount] 的不同
行,您可以使用它:AcctNo_Pkey
的数量.聚合结果中某处的 1500
You can use COUNT(DISTINCT )
on the output of a CASE
expression. For example, to count the number of distinct AcctNo_Pkey
s that have an [amount] < 1500
row somewhere in the aggregated result, you could use this:
COUNT(DISTINCT CASE WHEN [amount] < 1500 THEN AcctNo_PKey END)
您可以在在这个最小的 sqlfiddle 示例
这篇关于Case When Distinct 值然后求和另一个值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:Case When Distinct 值然后求和另一个值?


- 以一个值为轴心,但将一行上的数据按另一行分组? 2022-01-01
- 使用 Oracle PL/SQL developer 生成测试数据 2021-01-01
- 如何使用 pip 安装 Python MySQLdb 模块? 2021-01-01
- 远程 mySQL 连接抛出“无法使用旧的不安全身份验证连接到 MySQL 4.1+"来自 XAMPP 的错误 2022-01-01
- 如何将 SonarQube 6.7 从 MySQL 迁移到 postgresql 2022-01-01
- SQL 临时表问题 2022-01-01
- 导入具有可变标题的 Excel 文件 2021-01-01
- 在SQL中,如何为每个组选择前2行 2021-01-01
- 如何将 Byte[] 插入 SQL Server VARBINARY 列 2021-01-01
- 更改自动增量起始编号? 2021-01-01