CODEWITHSUNDEEP
sqlgoogle-bigquery
Mike5298
Mike5298

Reputation: 385

How can create a new column in Google BigQuery with SQL that is based on a calculation of existing columns?

I have some BigQuery code that is pulling the number of users who start a session on a website and the number of successful sessions from google analytics. The code is creating a temporary table.

SELECT
SUM(CASE
    WHEN eventAction = 'end' AND eventLabel = 'success' THEN uniqueevents END ) AS success,
 SUM(CASE
    WHEN eventAction = 'begin' AND eventLabel = 'begin' THEN uniqueevents END ) AS starts

I need to calculate the success rate (success/starts) in a new column of my temporary table. I've read a couple of posts that suggest using group by but I cant get my head around it.

Any suggestions?

Cheers

Upvotes: 0

Views: 126

Answers (1)

Ka Boom
Ka Boom

Reputation: 484

You can use a subquery to perform your calculation for success events and starts event, then calculate the final result. Here is the example:

WITH
  sample AS (
  SELECT
    'end' AS eventAction,
    'success' AS eventLabel,
    10 AS uniqueevents
  UNION ALL
  SELECT
    'begin',
    'begin',
    20 ),
  pre_cal AS (
  SELECT
    SUM(CASE
        WHEN eventAction = 'end' AND eventLabel = 'success' THEN uniqueevents
    END
      ) AS success,
    SUM(CASE
        WHEN eventAction = 'begin' AND eventLabel = 'begin' THEN uniqueevents
    END
      ) AS starts
  FROM
    sample )
SELECT
  success,
  starts,
  success/starts AS per
FROM
  pre_cal

Upvotes: 1

Related Questions