CODEWITHSUNDEEP
sqlgoogle-bigquery
nick
nick

Reputation: 71

Substraction by the column just created in bigquery

My query is supposedly create new column based on the keyword in data using bigquery. For example if in data consists 'Mike' it will create Mike column, 'John' will create John column and the list goes on.. However, I want to create a 'other' column that is the substraction of overall name with the column I just created.

My code example (wrong at SUBSTRACT function):

  SELECT 
   COUNT(Name) as n_name,
        SUM(CASE WHEN Name LIKE '%MIKE%' THEN 1 ELSE 0 END) AS Mike,
        SUM(CASE WHEN Name LIKE '%JOHN%' THEN 1 ELSE 0 END) AS John,
        SUM(CASE WHEN Name LIKE '%MICHAEL%' THEN 1 ELSE 0 END) AS Michael,
        .....
        SUBSTRACT (n_name ,Mike and John)  AS Others
     FROM t

Is there any way to do some substraction by the column I just created ?

Upvotes: 1

Views: 147

Answers (2)

chandu komati
chandu komati

Reputation: 795

 SELECT 
     *
     ,n_name - Mike - John - Michael AS Other
     FROM 
        (
            SELECT 
                COUNT(Name) AS n_name
                ,COUNT(CASE WHEN Name like '%MIKE%' THEN 1 END) AS Mike
                ,COUNT(CASE WHEN Name LIKE '%JOHN%' THEN 1 END) AS John
                ,COUNT(CASE WHEN Name LIKE '%MICHAEL%' THEN 1 END) AS Michael
            FROM 
                t
        ) aa

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

you can use below approach (BigQuery Standard SQL)

SELECT *, 
  n_name - Mike - John - Michael AS Other
FROM (
  SELECT 
    COUNT(Name) AS n_name,
    COUNTIF(Name LIKE '%MIKE%') AS Mike,
    COUNTIF(Name LIKE '%JOHN%') AS John,
    COUNTIF(Name LIKE '%MICHAEL%') AS Michael,
  FROM t 
)

Upvotes: 1

Related Questions