Count distinct values when using where and having

Transact-SQL
1

Hi

I'm trying to count the number of distinct occurrences while using where and having. However, the result isn't quite what I need. Although I get the data returned that I want it's returned as multiple rows; I just want one total.

Here is the query I've developed so far:

select
count(distinct hdd_smart_log.serial)
from
hdd_smart_log
inner join device_mst on device_mst.dev_id = hdd_smart_log.dev_id
where
hdd_smart_log.slice_date <= curdate()
and hdd_smart_log.slice_date >= curdate() - 7
group by
hdd_smart_log.serial
having
sum(pending_sector) > 800

And I receive this back from the query: (47 rows showing one per row)
image

What I would like is one result: 47.

Where is the error in my query?

Thanks

2 
; with cte as 
(
select
       hdd_smart_log.serial
	 , sum(pending_sector)
from
    hdd_smart_log
        inner join 
	device_mst 
	      on 
		    device_mst.dev_id = hdd_smart_log.dev_id
where
      hdd_smart_log.slice_date <= curdate()
              and 
      hdd_smart_log.slice_date >= curdate() - 7
group by  
           hdd_smart_log.serial
having 
      sum(pending_sector) > 800 
) 
select 
     count(distinct serial) 
from 
    cte
1 Like
3

harishgg1, thank you very much.

Worked just as I needed.