Andrew Andrew - 7 months ago 12
SQL Question

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

I am trying to make this very simple SQL script work:

SELECT
DATE(SEC_TO_TIMESTAMP(created_utc)) date_submission,
COUNT(*) AS num_apples_oranges_submissions
FROM
[fh-bigquery:reddit_comments.2008]
WHERE
(LOWER(body) CONTAINS ('apples')
AND LOWER(body) CONTAINS ('oranges'))
GROUP BY
date_submission
ORDER BY
date_submission


The results look like 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 you can see, for days where there were no submissions containing both "apples" and "oranges", instead of a value of 0 being returned, the entire row is simply missing (such as on the 12th and 14th).

How can I fix this? I'm at my wits end. Thank you.

Answer

Try below, it will return all submissions days

SELECT
  DATE(SEC_TO_TIMESTAMP(created_utc)) date_submission,
  SUM((LOWER(body) CONTAINS ('apples') AND LOWER(body) CONTAINS ('oranges'))) AS num_apples_oranges_submissions
FROM
  [fh-bigquery:reddit_comments.2008]
GROUP BY
  date_submission
ORDER BY
  date_submission 
Comments