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

I have following (simplified) query:

SELECT     ResolvedBy, COUNT(*) AS Count, fiCategory, fiSubCategory, fiSymptom
FROM         tContact
WHERE     (ResolvedBy IS NOT开发者_Go百科 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.

---

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

---

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