来自带有HAVING子句的SQL Query的COUNT结果

Are you able to use COUNT in a query with a HAVING clause so that the COUNT returns the number of rows? When I try, Im getting the count of the number of times the ID shows up in the table. Here is the query:

您是否能够在具有HAVING子句的查询中使用COUNT，以便COUNT返回行数？当我尝试时，我得到ID显示在表格中的次数。这是查询：

SELECT col_appid, min(col_payment_issued_date) as PayDate  
FROM tbl_ui_paymentstubs  
WHERE isnull(col_payment_amount,0) > 0  
GROUP BY col_appid  
HAVING min(col_payment_issued_date) >= '09/01/2010' and min(col_payment_issued_date) <= '09/30/2010'

I get back 6 rows, which is fine, but i'd like to just get back the number 6.

我回来了6行，这很好，但我想回到6号。

I found I could do it this way, but was wondering if there was another, more elegant way:

我发现我可以这样做，但想知道是否有另一种更优雅的方式：

WITH Claims_CTE(AppID, PayDate) as
(  
 SELECT col_appid, min(col_payment_issued_date) as PayDate
 FROM tbl_ui_paymentstubs
 WHERE isnull(col_payment_amount,0) > 0
 GROUP BY col_appid
 HAVING min(col_payment_issued_date) >= '09/01/2010' and min(col_payment_issued_date) <= '09/30/2010'
)  
 SELECT count(AppID) as Amount from Claims_CTE

3 个解决方案

#1

Using COUNT with a GROUP BY clause will provide a count for each group. If you want the count of the number of groups, it will have to be a separate query (like your CTE example).

将COUNT与GROUP BY子句一起使用将为每个组提供计数。如果要计算组数，则必须是单独的查询（如CTE示例）。

I would just use a simple subquery, instead of the CTE:

我只想使用一个简单的子查询，而不是CTE：

SELECT COUNT(*) FROM 
 (SELECT col_appid, min(col_payment_issued_date) as PayDate  
  FROM tbl_ui_paymentstubs  
  WHERE isnull(col_payment_amount,0) > 0  
  GROUP BY col_appid  
  HAVING
     min(col_payment_issued_date) >= '09/01/2010'
     and min(col_payment_issued_date) <= '09/30/2010') Claims

#2

You can also use a sub-query.

您还可以使用子查询。

SELECT count(*) as Amount
FROM (
    SELECT col_appid FROM tbl_ui_paymentstubs
    WHERE isnull(col_payment_amount,0) > 0
    GROUP BY col_appid
    HAVING min(col_payment_issued_date) BETWEEN '09/01/2010' AND '09/30/2010'
) Claims

#3

Assuming you have a table with the distinct list of col_appid values called App, this query also works and may be better performance, too:

假设你有一个表格，其中包含一个名为App的col_appid值的独特列表，这个查询也可以运行，也可能是更好的表现：

SELECT Count(*)
FROM
   App A
   CROSS APPLY (
      SELECT TOP 1 col_payment_issued_date
      FROM tbl_ui_paymentstubs P
      WHERE
         P.col_payment_amount > 0
         AND A.col_appid = P.col_appid
      ORDER BY col_payment_issued_date
   ) X
WHERE
   X.col_payment_issued_date >= '09/01/2010'
   AND X.col_payment_issued_date < '10/01/2010'

If there is no App table you can substitute (SELECT DISTINCT col_appid FROM tbl_ui_paymentstubs) A but that will not perform as well. It could still be a contender compared to the other queries given.

如果没有App表，您可以替换（SELECT DISTINCT col_appid FROM tbl_ui_paymentstubs）A，但这样做也不会。与其他查询相比，它仍然可能是一个竞争者。

Other notes:

其他说明：

You don't need to do isnull(column, 0) > 0 because column > 0 already excludes NULLs.

您不需要执行isnull（column，0）> 0，因为列> 0已经排除了NULL。
@ar's and @bdukes' queries don't need anything in the inner SELECT clause, they can just be SELECT 1 which may be a performance improvement (nothing else changes)

@ ar和@bdukes的查询在内部SELECT子句中不需要任何内容，它们可以只是SELECT 1，这可能是性能改进（没有其他更改）
I hope there's a constraint on col_payment_issued_date so that values do not have a time portion such as 11:23 AM, otherwise your BETWEEN clause will eventually not pull the correct data for the entire month.

我希望col_payment_issued_date有一个约束，这样值就没有时间部分，如11:23 AM，否则你的BETWEEN子句最终将无法提取整个月的正确数据。

Update

更新

For what it's worth, the date format '20100901' will work everywhere, with any language or DATEFIRST setting. I encourage you to get in the habit of using it. Other formats such as '09/01/2010' or '2010/09/01' and so on can get the month and the day mixed up.
对于它的价值，日期格式'20100901'将随处可用，使用任何语言或DATEFIRST设置。我鼓励你养成使用它的习惯。其他格式如'09 / 01/2010'或'2010/09/01'等可能会让月份和日期混乱。

@DScott said:

@DScott说：

There is an tbl_Application, but in this instance is not used. I could join to it, but im just counting payments for this query so it is not required.

有一个tbl_Application，但在这个例子中没有使用。我可以加入它，但我只计算此查询的付款，所以它不是必需的。

Would you mind trying my query and giving me feedback on its performance compared to the other methods? I am hoping that even with the extra join in the query, it performs pretty well.

您是否介意尝试我的查询并与其他方法相比给我反馈其性能？我希望即使在查询中额外加入，它也表现得非常好。

#1