Calculate percentage of group using GROUP BY

Question

I am doing a GROUP BY and COUNT(*) on a dataset, and I would like to calculate the percentage of each group over the total.

For example, in this query, I would like to know how much the count() for each state represents over the total ( select count() from publicdata:samples.natality ):

SELECT state, count(*)
FROM [publicdata:samples.natality]
GROUP by state

There are several ways to do it in SQL, but I haven't found a way to do it in Bigquery, does anyone know?

Thanks!

Answer 1

Check ratio_to_report, one of the recently announced window functions:

SELECT state, ratio * 100 AS percent FROM (
 SELECT state, count(*) AS total, RATIO_TO_REPORT(total) OVER() AS ratio
 FROM [publicdata:samples.natality]
 GROUP by state
)

state   percent
AL      1.4201828131159113   
AK      0.23521048665998198  
AZ      1.3332896746620975   
AR      0.7709591206172346   
CA      10.008298605982642