COUNT results from SQL Query with a HAVING clause

user_343291 0
Viewed 23951

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

`

greatest-n-per-group count sql-server sql
4 Answers

Use Below Query for Counting duplicate Record in Oracle.

SELECT Column1 , count(*) Num
FROM TABLE_NAME whe   
GROUP BY Column1 
HAVING count(*) > 1  
ORDER BY num desc;
user_71792410
Related