Group by columns + COUNT(*), how to get the average count for every combination?

Question

I have following (simplified) query:

SELECT     ResolvedBy, COUNT(*) AS Count, fiCategory, fiSubCategory, fiSymptom
FROM         tContact
WHERE     (ResolvedBy IS NOT NULL)
GROUP BY ResolvedBy, fiCategory, fiSubCategory, fiSymptom
ORDER BY Count DESC

Now i need the average count for every combination of fiCategory, fiSubCategory, fiSymptom as column. How to do that?

For example:

ResolvedBy    Count    fiCategory    fiSubCategory    fiSymptom    Average
    1           50         1              2             3            40
    2           30         1              2             3            40
    3           40         1              2             3            40
    1           20         2              3             4            30
    2           40         2              3             4            30

In the example are two combinations of fiCategory,fiSubCategory and fiSymptom: 1,2,3 and 2,3,4. Hence there are two averages that are calculated:

1. 50+30+40 / 3 = 40
2. 20+40 / 2 = 30.

So i want to sum the count of every combination and divide through the number of occurences.

Edit: The example is an extraction of the desired result of the query. The count is the sum of all occurences of this combination for every ResolvedBy.

Thank you in advance.

Answer 1

Select ResolvedBy, [Count], fiCategory, fiSubCategory, fiSymptom
    , Avg(Z.Count) Over( Partition By fiCategory, fiSubCategory, fiSymptom ) As AvgByGrp
From    (
        Select ResolvedBy, Count(*) As [Count], fiCategory, fiSubCategory, fiSymptom
        From tContact 
        Group By ResolvedBy, fiCategory, fiSubCategory, fiSymptom
        ) As Z

Order By Z.Count Desc

Answer 2

Try this:

SELECT main.ResolvedBy, COUNT(*) AS Count, 
    main.fiCategory, main.fiSubCategory, main.fiSymptom, average
FROM tContact main
JOIN (SELECT COUNT(*)/count(distinct ResolvedBy) as average,
      fiCategory, fiSubCategory, fiSymptom group by 2,3,4) x
        on x.fiCategory = main.fiCategory
        and x.fiSubCategory = main.fiSubCategory
        and x.fiSymptom = main.fiSymptom
WHERE main.ResolvedBy IS NOT NULL
GROUP BY 1, 3, 4, 5
ORDER BY 2 DESC