聚合函数的 where 子句条件 [英] Where clause condition on aggregate functions

问题描述

我有以下简单的查询，

SELECT US_LOGON_NAME as Username, 
COUNT(I.IS_ISSUE_NO) as Issues
FROM ISSUES I JOIN USERS U ON I.IS_ASSIGNED_USER_ID = U.US_USER_ID
WHERE I.IS_RECEIVED_DATETIME BETWEEN 20110101000000 AND 20110107000000
GROUP BY U.US_LOGON_NAME; 

我想在选择列表中添加额外的 COUNT() 函数但对它们施加某些 where 条件的地方.这是通过 CASE() 语句以某种方式完成的吗?我尝试将 Where 子句放在选择列表中，但这似乎是不允许的.我不确定这里是否真的需要子查询，但我不这么认为.

Where I want to add additional COUNT() functions to the select list but impose certain where conditions on them. Is this done with a CASE() statement somehow? I tried putting Where clauses inside the select list, and that doesn't seem to be allowed. I'm not sure if subqueries are really necessary here, but I dont think so.

例如，我想要一个 COUNT() 函数，它只计算某个范围内的问题，然后是另一个范围内或其他各种条件的问题，等等:

For example I want one COUNT() function that only counts issues within a certain range, then another in another range or with other assorted conditions, etc:

 SELECT US_LOGON_NAME as Username, 
 COUNT(I.IS_ISSUE_NO (condition here)
 COUNT(I.IS_ISSUE_NO (a different condition here)

等等...

仍按登录名分组.

谢谢.

推荐答案

SELECT
  SUM(CASE WHEN I.IS_ISSUE_NO (condition here) THEN 1 ELSE 0 END) AS COND1
  SUM(CASE WHEN I.IS_ISSUE_NO (condition here) THEN 1 ELSE 0 END) AS COND2

这篇关于聚合函数的 where 子句条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！