Group by and aggregation on Bigquery

Question

I have a table which has the following format (Google Big query) :

user	url	val1	val2	val3	...	val300
A	a	0.5	0	-3	...	1
A	b	1	2	3	...	2
B	c	5	4	-10	...	2

I would like to obtain a new table where I obtain the number of urls by user, and vals are aggregated by average. (The number of different vals can be variable so I would like to have something rather flexible)

user	nb_url	val1	val2	val3	...	val300
A	2	0.75	1	0	...	1.5
B	1	...

What is the good syntax?

Thank you in advance

Answer 1

Aggregate by user, select the count of URLs, and the average of the other columns.

SELECT
    user,
    COUNT(*) AS nb_url,
    AVG(val1) AS val1,
    AVG(val2) AS val2,
    AVG(val3) AS val3,
    ...
    AVG(val300) AS val300
FROM yourTable
GROUP BY user
ORDER BY user;