How to divide a Bigquery Column by sum of values in another column to find the ratio in Google Bigquery

Problem:

How to divide a column with sum of another column to find out the ratio

Example:

SELECT Col1/(SELECT SUM(Col2) FROM table)
FROM table

It throws the below error message:

Query Failed Error: Subselect not allowed in SELECT clause

Resolving Error: Subselect not allowed in SELECT clause

To achieve this do a cross join as shown below:

SELECT 
  Col1, 
  Col1 / total AS ratio
FROM `project.dataset.table`, 
(SELECT SUM(Col2) total FROM `project.dataset.table`)

mCollins

posted on 24 Apr 19