I'm using SQL Server 2000, and I thought I had this, but I was wrong, the following query returns everything I need with the exception of the casecounter. In the query below, I'm trying to return all the the items used by a physician grouping it by the procedure done and surgeon (here I'm limiting it to one for testing purposes) including returning the number of cases the data is pulled from (distinct meetingnumber). In my example, there should 5, my casecounter field is return various values on each record that range from 1 to 5. I'm assuming that it is returning how many cases that item was used on instead of the total of of procedures (of that type)for that surgeon.

Procedure      physician     item           casecnt
CABG           DOCTOR A        ITEM 1         5
CABG           DOCTOR A        ITEM 2         5

select det.PrimaryProcedure,
	det.PrimarySurgeon,
	det.PrimeDesc,
        det.CatalogNo,
	det.ItemNumber,
	det.ImmsField,
	sum(det.actualqty) as  totalused, 
	sum(det.actualqty * det.itemunitcost)as avgCost,
	count(det.meetingnumber) as CaseCounter
from
	(SELECT rtrim(dbo.coinv.primedesc) as PrimeDesc, 
	       rtrim(dbo.coinv.catalogno) as CatalogNo, 
	       rtrim(itemnumber) as ItemNumber, 
	       dbo.cocasedata.linkedid, 
	       dbo.coCaseData.ActualQty,
	       dbo.coCaseData.ItemUnitCost,
	       (dbo.coCaseData.ActualQty * ItemUnitCost) as ExtdCost,
	       (select isnull(immsfield,' ') from dbo.coinv i where i.invid=coCaseData.linkedid) as immsfield, 
	       (select isnull(CostBeforeCF,0) from dbo.coinv i where i.invid=coCaseData.linkedid) as CostBeforeCF, 
	       (select isnull(cf,' ') from dbo.coinv i where i.invid=coCaseData.linkedid) as CF,
		dbo.Get_PrimaryProcSurgeonByMeetID(meetings.meetingnumber) as PrimarySurgeon,
		dbo.Get_PrimaryProcDescByMeetID(Meetings.MeetingNumber) as PrimaryProcedure,
		(Select top 1 ProcID from dbo.meetproc where meetproc.meetingnumber=meetings.Meetingnumber order by sysorder) as ProcID,
		dbo.meetings.MeetingNumber
	 FROM   dbo.meetings INNER JOIN 
	       dbo.cocasedata ON dbo.meetings.meetingnumber = dbo.cocasedata.meetingnumber INNER JOIN 
	       dbo.coinv ON dbo.cocasedata.linkedid = dbo.coinv.invid 
	WHERE     (dbo.meetings.iscompleted = 1) 
	and meetings.begintime between @ltQueryFrom and @ltQueryTo 
	and coInv.invid<>@gnNonItemID 
	and timeChargeItem=0
	and coCaseData.ActualQty>0
	AND meetings.deptid=@lnDeptID ) as det
where det.PrimarySurgeon='AMEIKA, JAMES' and procID=1118
group by PrimaryProcedure,PrimarySurgeon,primedesc,catalogno,itemnumber,immsField
Order By PrimaryProcedure, PrimarySurgeon

Thanks for any help, sorry for the length of the post

Kirk