using aggregate function in select to create an array in bigquery

Question

I am getting an error "SELECT without FROM clause cannot use aggregation at [45:79]" while trying to use SUM in SELECT clause while passing to an UDF. Below is a simplified version of my query

CREATE TEMP FUNCTION COL_AVG(input ANY TYPE)
AS ((SELECT AVG(x) FROM UNNEST(input) AS x));

with input as (select 'a' name, 1 val UNION ALL
select 'a' name, 2 val UNION ALL
select 'a' name, 3 val UNION ALL
select 'b' name, 6 val UNION ALL
select 'b' name, 2 val UNION ALL
select 'c' name, 3 val UNION ALL
select 'c' name, 7 val )

select COUNT(1) tot_count, SUM(M_A) M_A, SUM(M_B) M_B, SUM(M_C) M_C, COL_AVG((SELECT [SUM(M_A),SUM(M_B),SUM(M_C)])) my_avg
FROM (
select case when name = 'a' THEN SUM(val) END M_A,
 case when name = 'b' THEN SUM(val) END M_B,
  case when name = 'c' THEN SUM(val) END M_C,
from input
group by name
) x

Can someone help in understanding whats wrong here "COL_AVG((SELECT [SUM(M_A),SUM(M_B),SUM(M_C)])) my_avg" and how this can be achieved?