Very simple BigQuery SQL script won't return "0" for Count rows with no results -


i trying make simple sql script work:

select   date(sec_to_timestamp(created_utc)) date_submission,   count(*) num_apples_oranges_submissions   [fh-bigquery:reddit_comments.2008]   (lower(body) contains ('apples')   , lower(body) contains ('oranges')) group   date_submission order   date_submission 

the results this:

1   2008-01-07  3     2   2008-01-08  1     3   2008-01-09  2     4   2008-01-10  3     5   2008-01-11  2     6   2008-01-13  2     7   2008-01-15  2     8   2008-01-16  3     

as can see, days there no submissions containing both "apples" , "oranges", instead of value of 0 being returned, entire row missing (such on 12th , 14th).

how can fix this? i'm @ wits end. thank you.

try below, return submissions days

select   date(sec_to_timestamp(created_utc)) date_submission,   sum((lower(body) contains ('apples') , lower(body) contains ('oranges'))) num_apples_oranges_submissions   [fh-bigquery:reddit_comments.2008] group   date_submission order   date_submission  

Comments

Popular posts from this blog

java - nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet Hibernate+SpringMVC -

sql - Postgresql tables exists, but getting "relation does not exist" when querying -

asp.net mvc - breakpoint on javascript in CSHTML? -