I'm trying to get a calculation at the end of the table that averages each column. I can't seem to get my head working right on this.
This is my query:
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
Resulting data from the query:
| id | min |
|---|---|
| 1 | 31 |
| 2 | 11 |
| 3 | 21 |
| 4 | 8 |
| 25 | 9 |
| 26 | 6 |
I'd like to have a row at the end with an average calculation for each column e.g.:
| id | min |
|---|---|
| 1 | 31 |
| 2 | 11 |
| 3 | 21 |
| 4 | 8 |
| 25 | 9 |
| 26 | 6 |
| 10.16666667 | 14.33333333 |
Much appreciate any help.
CodePudding user response：
The best I can think of is a union with the previous query. This is a quick fix what I came up
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