我試圖在表格末尾計算每列的平均值。我似乎無法正確地處理這個問題。
這是我的查詢：
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 |
非常感謝任何幫助。
uj5u.com熱心網友回復：
我能想到的最好的方法是與前面的查詢合并。這是我想出的快速修復
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/453094.html