Geek Answers Handbook

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

I have the following (simplified) request:

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 for each combination fiCategory, fiSubCategory, fiSymptom as a column. How to do it?

For instance:

 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

The example shows two combinations of fiCategory, fiSubCategory and fiSymptom: 1,2,3 and 2,3,4 . Therefore, two averages are calculated:

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

So, I want to summarize the count of each combination and divide by the number of occurrences.

Change An example is retrieving the desired query result. The score is the sum of all occurrences of this combination for each ResolvedBy .

Thanks in advance.

+4
source share
2 answers
 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
+7
source

Try the following:

 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
+2
source

More articles:


All Articles