Average a count - howto?? 1

[sparkbyte]

I have tried several ways to do this but cannot seem to figure it out.

Here is the last trial

select count(distinct BoxNumber) As TtlBoxCount,
	(Select count(*)
		from dbo.tblTrackingTable
		where TrackingDate > '9/20/2010'
		and BoxNumber Like 'NBC%') as TTlFilesBox,
	avg(TTlFilesBox)
	
From dbo.tblTrackingTable
	where TrackingDate > '9/20/2010'
		and BoxNumber Like 'NBC%'

Thanks

John Fuhrman

 

[seaport]

I am not sure about your table structure but you may try something like that.

with a as (
Select count(*) as TTlFilesBox
from dbo.tblTrackingTable
where TrackingDate > '9/20/2010'
        and BoxNumber Like 'NBC%'
Group By BoxNumber)
select avg(TTlFilesBox), count(*)
from a

 

[sparkbyte]

Thanks, that was just the starter I needed.



Thanks

John Fuhrman