COUNT results from SQL Query with a HAVING clause

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).

I would just use a simple subquery, instead of the 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

You can also use a sub-query.

SELECT count(*) as AmountFROM (    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

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:

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   ) XWHERE   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.

Other notes:

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

• @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)

• 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.

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.

@DScott said:

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.

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.