by a derived table, do you mean a temporary table?

THANK YOU

It helped and worked!

jt-75...I tried both versions that you provided, yet I still get:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Now...

thank you for your reply jt-75

Yes, it's hours:minutes:seconds.  And I want to find the average and have the output be the same(hours:minutes:seconds).

Hey, I answered my own question! 

I just changed my GROUP BY clause to look like:

GROUP BY RGnameLast, RGnameFirst, COcity, SRnameLast, SRnameFirst, WRreceivedDate, WRbillToFIleId, WCvideoAmount, WRinjury, WTbudgetAmount, WTstatusId,...

The query works until I try and put the COUNT, SUM, and AVG statements in it. So without the added functions, it works like it should.  But I need to...

I thought SQL Server only counted the column you had the COUNT clause operated on. For example,

SELECT id, color, date, type, COUNT(type) AS 'Quantity'

FROM CatalogTable

WHERE date > '01/01/2005'

AND color =...

Thanks guys, using CUBE sounds like a wonderful solution!

That's a good idea, I will just select based on street, ave, etc...and then scan through it by hand...all 8953 rows!

I agree, I pretty much came to the same conclusion.  By hand it is then!

BTW,

Here is a sample of the resultset that I am trying to get...

smay jjohnson Dept1 IO0 315 192 316 94.971272151898717 30010.921999999995 21.0 15.800000000000001 405 7

smay jjohnson Dept2 IO3 454 342...

Carl, when I put your query in the original query, I am still getting dupes....

Here is my resultset:

cgrtji cgrtji Dept1 IO0 0 0 7 70.847142857142856 495.92999999999995 NULL 0.34999999999999998 0 0

cgrtji cgrtji Dept2 IO3 0 0 7 70.847142857142856 495.92999999999995 NULL 0.34999999999999998 0 0

dbabic sptyo Dept1 IO0 0 0 0 NULL NULL NULL NULL 2 0

rmon aoier NULL NULL NULL NULL 0 NULL NULL NULL NULL 0 0

rmon cl Dept2 IO3 0 0 0 NULL NULL NULL NULL 0 0

rmon rmon Dept1 IO0 103 106 91 72.909890109890114 6634.8000000000002 3.0 4.5499999999999998 42 0

rmon rmon Dept2 IO3 103 106 91 72.909890109890114 6634.8000000000002 3.0 4.5499999999999998 42 0

rmon smay NULL NULL NULL NULL 0 NULL NULL NULL NULL 0 0

rmon tboks NULL NULL NULL NULL 0 NULL NULL NULL NULL 0 0

rmon wseifried NULL NULL NULL NULL 0 NULL NULL NULL NULL 0 0

smay eramsey NULL NULL NULL NULL 0 NULL NULL NULL NULL 0 0

smay rmith Dept1 IO0 280 62 262 84.577519083969477 22159.310000000001 17.0 13.1 253 0

smay rmith Dept2 IO3 280 62 262 84.577519083969477 22159.310000000001 17.0 13.1 253 0

smay jjohnson Dept1 IO0 315 192 316 94.971272151898717 30010.921999999995 21.0 15.800000000000001 405 7

smay jjohnson Dept2 IO3 315 192 316 94.971272151898717 30010.921999999995 21.0 15.800000000000001 405 7

smay kauben Dept1 IO0 300 90 248 90.412943548387048 22422.409999999989 14.0 12.4 288 5

smay kauben Dept2 IO3 300 90 248 90.412943548387048 22422.409999999989 14.0 12.4 288 5

smay jjackson Dept1 IO0 244 103 227 112.52594713656386 25543.389999999996 15.0 11.35 236 0

smay jjackson Dept2 IO3 244 103 227 112.52594713656386 25543.389999999996 15.0 11.35 236 0

smay mldako Dept1 IO0 262 149 204 73.003137254901972 14892.640000000003 11.0 10.199999999999999 297 46

smay mldako Dept2 IO3 262 149 204 73.003137254901972 14892.640000000003 11.0 10.199999999999999 297 46

smay netvich Dept1 IO0 288 194 295 102.20257627118646 30149.760000000006 19.0 14.75 331 0

smay netvich Dept2 IO3 288 194 295 102.20257627118646 30149.760000000006 19.0 14.75 331 0

smay nloer Dept1 IO0 309 167 285 74.296070175438658 21174.380000000016 19.0 14.25 394 0

smay nloer Dept2 IO3 309 167 285 74.296070175438658 21174.380000000016 19.0 14.25 394 0

smay slupin Dept1 IO0 361 85 341 107.1750146627566 36546.68 21.0 17.050000000000001 284 4

smay slupin Dept2 IO3 361 85 341 107.1750146627566 36546.68 21.0 17.050000000000001 284 4

smay smay Dept1 IO0 54 74 64 116.69890625000004 7468.7300000000023 3.0 3.2000000000000002 16 0

smay smay Dept2 IO3 54 74 64 116.69890625000004 7468.7300000000023 3.0 3.2000000000000002 16 0

As you can see, all the data after the Dept(1 or 2) is...

INSERT INTO [users] ([Uid],[UinternalOfficeID],[UworkgroupTypeID],[Uname],[UpID],[UnameFirst],[UnameLast],[Utitle],[Uphone],[Ufax],[Uemail],[UcaseManager],[UenteredBy],[UenteredDate],[UmodifiedBy],[UmodifiedDate],[Upassword],[Upermissions],[Uhash],[ULocation])VALUES('US72','IO3','TRUTXXX','ejohnson','ejoh','Endia','Johnson','Case Worker',NULL,NULL,'ejohnson@litsol.com','kmccollum','junkman','Jan 26 2004 12:00:00:000AM','junkman','Apr  5 2004  1:33:51:000PM','TerMinateD','USR','81153238378496524708842909949','P')

INSERT INTO [workrequest] ([WRid],[WRreceivedDate],[WRattorneyFileID],[WRdateOfAccident],[WRrequestSubjectID],[WRinsuredName],[WRrequestGeneratorID],[WRfileContactID],[WRbillToID],[WRbillToFileID],[WRlegalCaseID],[WRcorrespTo],[WRenteredBy],[WRenteredDate],[WRmodifiedBy],[WRmodifiedDate],[WRcaseWorker],[WRcaseManager],[WRarchivedBy],[WRarchivedDate],[WRinternalOfficeID])VALUES('WR25310','Jul  6 2005  9:33:07:000AM','12541AL/RSO','May 25 1997 12:00:00:000AM','RS8799','','RG18528','RG3842','RG627','010 950 142 493','',1,'jjohnson','Jul  6 2005  9:33:07:000AM','jjohnson','Jul  6 2005  9:48:55:000AM','jjohnson','smacik',NULL,NULL,'IO0')

INSERT INTO...

One more time? PLEASE 

just kidding!

So I run that insert SP on every table in my query, then just cut & paste the results of the SP here?