COUNT results from SQL Query with a HAVING clause

Question

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:

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.

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

`

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

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

Answer 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

Answer 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:

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.

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.