Jeff, the query you gave me gives incorrect average, anyway. So the following query shows how I get just the correct Average#ofLineItemsPerDistinctInvoice for any given month. It's what I need in the final query as well.

WITH

cte AS

(

SELECT Invoice

,D_Type

,Status

,Yr = YEAR(RemediationDate)

,Mo = LEFT(DATENAME(mm,RemediationDate),3)

,RemediationDate

FROM dbo.Remediation

)

SELECT

Yr,

Mo,

--D_Type,

--Status,

--Invoice,

Distinct#Invoice = count(distinct Invoice),

#LineItems = COUNT(*),

#Avg#LineItemsPerInvoice = COUNT(*)/count(distinct Invoice)

FROM cte

group by

Mo,

Yr

--D_Type,

--Status,

--Invoice

which gives following correct averages

select 'Yr','Mo','Distinct#Invoice','#LineItems','#AvgLineItemsPerDistinctInvoice' union all

select '2014','Feb','2','6','3' union all

select '2014','Jan','2','12','6'

When I uncomment D_Type, Status, Invoice from above query I get the following result set:

select 'Mo', 'Yr', 'D_Type', 'Status', 'Invoice', '#LineItems', 'Distinct#Invoice' UNION ALL

select 'Feb', '2014', 'Recycle', 'Pass', '503', '1', '1' UNION ALL

select 'Feb', '2014', 'Recycle', 'Pass', '503', '3', '1' UNION ALL

select 'Feb', '2014', 'Remarket', 'Fail', '503', '1', '1' UNION ALL

select 'Feb', '2014', 'Reuse', 'Fail', '503', '1', '1' UNION ALL

select 'Jan', '2014', 'Recycle', 'Fail', '501', '1', '1' UNION ALL

select 'Jan', '2014', 'Recycle', 'Pass', '501', '1', '1' UNION ALL

select 'Jan', '2014', 'Recycle', 'Pass', '502', '1', '1' UNION ALL

select 'Jan', '2014', 'Remarket', 'Fail', '501', '1', '1' UNION ALL

select 'Jan', '2014', 'Remarket', 'Pass', '502', '3', '1' UNION ALL

select 'Jan', '2014', 'Reuse', 'Fail', '502', '3', '1' UNION ALL

select 'Jan', '2014', 'Reuse', 'Pass', '501', '1', '1' UNION ALL

select 'Jan', '2014', 'Reuse', 'Pass', '502', '1', '1'

Problem with it is that it now gives the count of distinct invoices per D_Type, Status, Invoice which will always be one. I need count distinct for each Yr,Mo grouping only, for eg. In January there were 6 line items and two distinct invoices, so average#ofLineItems is 6/2 = 3

The distinct is definitely the challenge here. Is there any solution in SQL, (maybe a ROLLUP by count of Distinct Invoices per month?) to build this query so I can do the aggregations for D_Type, Status, and Invoice, and also show Avg#ofLineItems/CountofDistinctInvoices per Month, in Excel?