Google BigQuery CASE function

Question

I'm trying to run a query like what's answered here, 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 that, I get the error message

"message": "Unrecognized function CASE".

If I try a much more simple (from an sql tutorial),

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

I then get

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

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

Seems that indeed it not supports CASE clause developers.google.com/bigquery/docs/query-reference — Yaroslav
– Yaroslav, Commented Jul 25, 2012 at 10:01
Yeah, I did check there alright, but the fact that they give version numbers led me to thinking that there was potentially an area where I could swap my version to a newer one that supports it. Very frustrating, it'd make the queries I need much easier. — seaders
– seaders, Commented Jul 25, 2012 at 10:32
CASE is supported BUT just as 'searched case' and not 'simple case' expression (as of 24th Feb 2016). Just in case somebody stumbles upon and is wondering, took me too long to figure it out — Silver
– Silver, Commented Feb 24, 2016 at 15:55

Felipe Hoffa · Accepted Answer · 2013-11-22 01:56:03Z

41

2013 update: BigQuery supports CASE:

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

'one'

answered Nov 22, 2013 at 1:56

Felipe Hoffa

59.8k23 gold badges185 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

seaders Over a year ago

Thanks for updating with the much better answer for anyone looking for that now. I'm not working with BigQuery any more, but I may be again in the future and the more things like above that it's adding makes it all the more appealing.

Tim Swena Over a year ago

What's the standard SQL version?

Tim Swena Over a year ago

Found it. It's also CASE cloud.google.com/bigquery/docs/reference/standard-sql/…

Jordan Tigani · Accepted Answer · 2012-07-25 16:19:27Z

6

The way to do this in BigQuery is to 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

answered Jul 25, 2012 at 16:19

Jordan Tigani

26.7k5 gold badges63 silver badges64 bronze badges

1 Comment

Felipe Hoffa Over a year ago

2013 update: BigQuery supports case: SELECT CASE WHEN x=1 THEN 'one' WHEN x=2 THEN 'two' ELSE 'more' END FROM (SELECT 1 AS x)

Collectives™ on Stack Overflow

Google BigQuery CASE function

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related