24 Apr 19 · npack · #bigquery ·   Bookmark   ×

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`)

npack

posted on 24 Apr 19

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds