如何取一个count(*)结果列的标准差和和

Question

我有一个这样的表：

firstname  lastname   values
john       doe        first
john       doe        second
john       doe        third
joe        public     first
joe        public     second
robert     willians   first
robert     willians   second

我想统计一下每个名字对出现的次数。然后计算计数列的总和、平均值和标准偏差。

目前，我有：

select stddev(t.count(*)), avg(t.count(*)), sum(t.count(*))
from (select count(*), firstname, lastname
from table
group by firstname, lastname) t

这提供

未知的用户定义函数t；count(("；，t."；count(("；，t."；count(*("；

Answer 1

只需使用一个更有意义的列别名：

select stddev(t.cnt), avg(t.cnt), sum(t.cnt)
from (select count(*) as cnt, firstname, lastname
from table
group by firstname, lastname
) t

Answer 2

您需要为COUNT(*)列分配一个别名

select stddev(t.count), avg(t.count), sum(t.count)
from (select count(*) AS count, firstname, lastname
from table
group by firstname, lastname) t