Geek asks and answers

Google BigQuery CASE Feature

Am I trying to run a query, for example, an answer, SQL Selecting multiple sums?

SELECT  SUM(CASE WHEN order_date >= '01/01/09' THEN quantity ELSE 0 END) AS items_sold_since_date,
    SUM(quantity) AS items_sold_total,
    product_ID
FROM    Sales
GROUP BY product_ID

But if I try, I get an error message

"message": "Unrecognized function CASE".

If I try a lot easier (from sql tutorial),

SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;

Then i get

"message": "searched case expression not supported at: 1.8 - 1.65".

I'm going to take a wild hit in the dark and assume that the SQL CASE function as a whole is simply not supported on BigQuery, but I really hope I'm wrong because it makes a huge difference based on the report of the queries I want to run.

+5
source share
2 answers
Update

2013: BigQuery supports CASE:

SELECT CASE WHEN x=1 THEN 'one' WHEN x=2 THEN 'two' ELSE 'more' END 
FROM (SELECT 1 AS x) 

'one'
+15
source

In BigQuery, you can use the if (test, then else) function. For example: SELECT sum(if (revision_id > 10, num_characters, 0)) FROM [publicdata:samples.wikipedia] or similar to your second query:

SELECT if (revision_id == 1, 'one', (if (revision_id == 2, 'two', 'more'))) FROM [publicdata:samples.wikipedia] limit 100

+5

More articles:


All Articles