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)\nFROM table\n

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 \n  Col1, \n  Col1 / total AS ratio\nFROM `project.dataset.table`, \n(SELECT SUM(Col2) total FROM `project.dataset.table`)\n

mCollins

posted on