Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

BigQuery Subtract Counts of Two Tables?

In MySQL I can do SELECT (SELECT COUNT(*) FROM table1) - (SELECT COUNT(*) FROM table2) to get the difference in counts between two tables. When I try this in BigQuery, I get: Subselect not allowed in SELECT clause. How do I run a query like this in BigQuery?

like image 888
Eli Avatar asked Jun 10 '14 00:06

Eli


People also ask

How do you use the minus function in BigQuery?

Though there is no MINUS function in BigQuery, you can use a LEFT OUTER JOIN as an alternative. it seems to me that 'IS NULL' was correct? you add matching B parts to A, so where there is no match, B vals will be NULL and that are the records that would also result from the MINUS..?

What is NaN in BigQuery?

Hence NaN is interpreted by BigQuery as null since it is considered as an invalid value. A value can be a string in double quotes, or a number, or true or false or null, or an object or an array.

What is safe offset in BigQuery?

Safe Offset - Avoid “Out of Bounds” Errorwhich is results in NULL values for not existing elements.

What is equivalent of NVL in BigQuery?

COALESCE is the equivalent for NVL function in Bigquery.


1 Answers

2019 update:

The original question syntax is now supported with #standardSQL

SELECT (SELECT COUNT(*) c FROM `publicdata.samples.natality`) 
  - (SELECT COUNT(*) c FROM `publicdata.samples.shakespeare`) 

As subselects are not supported inside the SELECT clause, I would use a CROSS JOIN for this specific query:

SELECT a.c - b.c
FROM
  (SELECT COUNT(*) c FROM [publicdata:samples.natality]) a
CROSS JOIN
  (SELECT COUNT(*) c FROM [publicdata:samples.shakespeare]) b
like image 123
Felipe Hoffa Avatar answered Oct 27 '22 20:10

Felipe Hoffa