SQL Server-每月平均数 [英] SQL Server - monthly avg of count

问题描述

我希望能够找出计数的每月平均值

I want to be able to find out the monthly average of a count

此刻我的代码是

SELECT 
    company, 
    COUNT(company) AS 'count'
FROM Information
GROUP BY company

我基本上需要它

SELECT company, 
       count(company) as 'count'
       avg(count(company)) per month as 'average'
FROM Information
group by company

我希望结果看起来像这样

I want the result to look something like this

company count   monthly average
a       5       6
b       13      14
c       2       2
d       45      45
e       23      21
f       6       5

推荐答案

一种非常简单的方法是先对每个公司和月份进行计数，然后对这些数据进行汇总以得出每个公司的总数和平均值.

A very simple approach would be to count per company and month first and then aggregate this data to get total and avarage per company.

select
  company,
  sum(cnt) as records,
  avg(cnt) as records_per_month
from
(
  select company, year(start_date), month(start_date), count(*) as cnt
  from information
  group by company, year(start_date), month(start_date)
) agg
group by company;

但是请阅读我对您问题的评论.

But read my comment to your question.

这篇关于SQL Server-每月平均数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！