我试图在表格末尾计算每列的平均值。我似乎无法正确地处理这个问题。
这是我的查询：
SELECT profiles.id, min (total_connections_average) from profiles
inner join connections ON from_profile_id = profiles.id
inner join (
select sq.from_profile_id,
count (sq.countn) as total_connections_average
from (
select from_profile_id, cast (accepted_at as date), count (*) as countn from connections
group by from_profile_id, cast (accepted_at as date)
) as sq
group by sq.from_profile_id
) sq ON sq.from_profile_id = profiles.id
where profiles.id in ('1','2','3','4','25','26')
group by profiles.id
查询的结果数据：
| ID | 分钟 |
|---|---|
| 1 | 31 |
| 2 | 11 |
| 3 | 21 |
| 4 | 8 |
| 25 | 9 |
| 26 | 6 |
我想在最后有一行，对每一列进行平均计算，例如：
| ID | 分钟 |
|---|---|
| 1 | 31 |
| 2 | 11 |
| 3 | 21 |
| 4 | 8 |
| 25 | 9 |
| 26 | 6 |
| 10.16666667 | 14.33333333 |
非常感谢任何帮助。
我能想到的最好的方法是与前面的查询合并。这是我想出的快速修复
SELECT profiles.id, min (total_connections_average) min from profiles
inner join connections ON from_profile_id = profiles.id
inner join (
select sq.from_profile_id,
count (sq.countn) as total_connections_average
from (
select from_profile_id, cast (accepted_at as date), count (*) as countn from connections
group by from_profile_id, cast (accepted_at as date)
) as sq
group by sq.from_profile_id
) sq ON sq.from_profile_id = profiles.id
where profiles.id in ('1','2','3','4','25','26')
group by profiles.id
#this is union for average last column
union
select avg(agg.id), avg(agg.min)
from (
SELECT profiles.id, min (total_connections_average) min from profiles
inner join connections ON from_profile_id = profiles.id
inner join (
select sq.from_profile_id,
count (sq.countn) as total_connections_average
from (
select from_profile_id, cast (accepted_at as date), count (*) as countn from connections
group by from_profile_id, cast (accepted_at as date)
) as sq
group by sq.from_profile_id
) sq ON sq.from_profile_id = profiles.id
where profiles.id in ('1','2','3','4','25','26')
group by profiles.id
) agg
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句