sum of count using distinct

[R7Dave]

Hello

I would like to return the sum but I am returning individual records with "1"

select 
(count (distinct( DirstatPolState+DirstatPolAcctNbr+PolSerialNbr+XdirstatPolicyYear) ))
from dirstat_record_bk1yr
where
	dirstatcoverage=94
	and DirstatPolState = 37

group by 
	DirstatPolState+DirstatPolAcctNbr+PolSerialNbr+XdirstatPolicyYear
having sum(XdirstatWritPrem) > 0 and
	sum(XdirstatWritPrem) < 25000

this returns 9 rows containg the "1"

what I want is 1 record containing the 9 (the sum)

Thanks in advance
Dave

(sorry, I accidentally posted this same question as a response to an earlier post)

 

[gmmastros]

This is where a derived table would come in handy. Stay tuned for a sql2005+ version using a common table expression. (I can tell someone will post it soon ).

[!]Select Sum(ColumnAlias) As [Whatever Name You Want]
From   ([/!]
select
(count (distinct( DirstatPolState+DirstatPolAcctNbr+PolSerialNbr+XdirstatPolicyYear) )) [!]As ColumnAlias[/!]
from dirstat_record_bk1yr
where
    dirstatcoverage=94
    and DirstatPolState = 37

group by
    DirstatPolState+DirstatPolAcctNbr+PolSerialNbr+XdirstatPolicyYear
having sum(XdirstatWritPrem) > 0 and
    sum(XdirstatWritPrem) < 25000
[!]    ) As A[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Crowley16]

don't look at me, I don't have time to read that complicated derived table statement...

--------------------
Procrastinate Now!

 

[R7Dave]

Thats what I needed - thank you - and I will also look into CTE.

thanks again
Dave

 

[markros]

BTW, I responded in other thread without looking at this one. I had an impulse to write it with CTE, but got lazy.

PluralSight Learning Library