I am trying to get the query below to group on procid, so that the numberdone represents how many where done. Currently, it lists each one out. I believe this is because I have as the third group by item the meetingnumber (which represents the basic case). But if I remove the meetingnumber from the group by clause, I get an error saying that the column meetingnumber is invalid in the select list, yata yata yata.

I have tried putting count(meetingnumber) in the primary select statement but that didn't work either.

Here is the query, results with it running but not grouped are below it:

SELECT  meetproc.procid,count(dbo.meetproc.procid) as numberdone,dbo.itable.company,
	(select description from coProc p where p.procid=meetproc.procid) as description,
	avg(datediff(mi,meetings.enteror,meetings.exitor)) as timeused, 
	avg(datediff(mi,meetings.enteror,meetings.exitor) * meetings.DeptCostPerMinute) as AvgORCost,
        (select isnull(sum(cocasedata.itemunitcost * coCaseData.ActualQty),0)/count(meetproc.procid)
		FROM dbo.meetings t3 INNER JOIN  
                dbo.meetproc t4 ON t3.meetingnumber = t4.meetingnumber INNER JOIN 
                dbo.cocasedata ON t3.meetingnumber = dbo.cocasedata.meetingnumber 
		where t4.procid=meetproc.procid  
		and t3.meetingnumber = meetings.meetingnumber
		and t3.begintime between @ltQueryFrom and @ltQueryTo 
		and t3.iscompleted=1 and t3.deptid=@lnDeptID ) as avgResCost

FROM   dbo.patients INNER JOIN
       dbo.meetings ON dbo.patients.patientid = dbo.meetings.patientid INNER JOIN
       dbo.meetproc ON dbo.meetings.meetingnumber = dbo.meetproc.meetingnumber INNER JOIN
       dbo.itable ON dbo.patients.primarycarrierid = dbo.itable.itableid
where meetings.iscompleted=1
and meetings.begintime between @ltQueryFrom and @ltQueryTo
Group by company,meetproc.procid,meetings.meetingnumber
ORDER BY dbo.itable.company,description

Results

procid      numberdone  company                        description                               
----------- ----------- ------------------------------ ------------------------------------------
7432        1           ATHENS AREA HEALTH PLAN SELECT LAMINECTOMY                               
7141        1           ATHENS AREA HEALTH PLAN SELECT LAPAROSCOPIC CHOLECYSTECTOMY              
7141        1           ATHENS AREA HEALTH PLAN SELECT LAPAROSCOPIC CHOLECYSTECTOMY              
7223        1           ATHENS AREA HEALTH PLAN SELECT MMK / MARSHALL-MARCHETTI-KRANTZ PROCEDURE 
7297        1           ATHENS AREA HEALTH PLAN SELECT TAH / TOTAL ABDOMINAL HYSTERECTOMY        
7298        1           ATHENS AREA HEALTH PLAN SELECT TVH / VAGINAL HYSTERECTOMY                

So in the data above there 2&3 records should be group and numberdone should be 2 for that row.

Thanks for any help
Kirk