SQL – get an AVG from a COUNT

how you doing?

I’m trying to get the average from a count. The metric is a string, so I get an error. I’ve tried some ways, but I can’t. Thanks for your help

• This is the code

SELECT 
user_type, // works fine
newsletter, // works fine
COUNT (newsletter) as total, // Works fine 
AVG (newsletter) as percentage, // Error. No matching signature for aggregate function AVG: Supported signatures AVG(INT64), AVG(NUMERICAL), AVG (FLOAT64)

This is what I’ve unsuccessfully tried

• AVG (newsletter) as percentage
• AVG (CAST (newsletter as INT64)) as percentage
• COUNT(newsletter) / SUM(newsletter)

I would like to get a table like this

user_type | newsletter | total | percentage
  free.        yes.       4.       x%
  premium.     yes.       7.       x%

Answer

To get the ratio of the current row to the whole table…

• you already have the value for each individual row
• use window functions to get the total for the whole table
• then divide the two

(With a “window” of () to represent the whole table)

x * 1.0 / SUM(x) OVER ()

In your case, x is COUNT(newsletter) which gives…

COUNT(newsletter) * 1.0 / SUM(COUNT(newsletter)) OVER ()