Jeff, it's not quite there yet. Definitely, the problem is that I need average # of lineItems per distinct Invoice.

So, for Jan 2014, where there are12 lineitems spread over 2 distinct invoices, the average is 6. For Feb 2014 there are 6 lineitems spread over 2 distinct invoices, the average is 3. Year totals will be 18 lineitems/4 dstinct invoices=4.5 avg number of lineitems per invoice.

IMO, results should look like this:

SELECT 'D_Type', 'Status', 'Yr', 'Mo', '#LineItems', 'Avg#LineItemsPerInvoice' UNION ALL

SELECT NULL, 'Fail', '2014', 'Feb', '2', '3' UNION ALL

SELECT NULL, 'Fail', '2014', 'Jan', '5', '6' UNION ALL

SELECT NULL, 'Fail', '2014', NULL, '7', '3' UNION ALL

SELECT NULL, 'Pass', '2014', 'Feb', '4', '3' UNION ALL

SELECT NULL, 'Pass', '2014', 'Jan', '7', '6' UNION ALL

SELECT NULL, 'Pass', '2014', NULL, '11', '4.5' UNION ALL

SELECT 'Recycle', NULL, '2014', 'Feb', '4', '3' UNION ALL

SELECT 'Recycle', NULL, '2014', 'Jan', '3', '6' UNION ALL

SELECT 'Recycle', NULL, '2014', NULL, '7', '4.5' UNION ALL

SELECT 'Remarket', NULL, '2014', 'Feb', '1', '3' UNION ALL

SELECT 'Remarket', NULL, '2014', 'Jan', '4', '6' UNION ALL

SELECT 'Remarket', NULL, '2014', NULL, '5', '4.5' UNION ALL

SELECT 'Reuse', NULL, '2014', NULL, '5', '4.5' UNION ALL

SELECT 'Reuse', NULL, '2014', 'Feb', '1', '3' UNION ALL

SELECT 'Reuse', NULL, '2014', 'Jan', '5', '6'

The problem I'm having is that distinct is disallowed with the Over() clause so I can't partition like follows:

WITH

cte AS

(

SELECT Invoice

,D_Type

,Status

,Yr = YEAR(RemediationDate)

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

,RemediationDate

FROM dbo.Remediation

)

SELECT D_Type

,Status

,Yr

,Mo

,#LineItems = COUNT(*)

,Avg#LineItemsPerInvoice = COUNT(*)/COUNT(DISTINCT Invoice) over (partition by yr, mo)

FROM cte

GROUP BY GROUPING SETS

(

(D_Type, Yr, Mo)

,(D_Type, Yr)

,(Status, Yr, Mo)

,(Status, Yr)

)

;

exact error is:

Msg 10759, Level 15, State 1, Line 17

Use of DISTINCT is not allowed with the OVER clause.