CODEWITHSUNDEEP
google-bigquery
thstart
thstart

Reputation: 427

How to combine aggregate results?

I have two queries:

Q#1:

SELECT
  DISTINCT Col1,
  COUNT(Col1) AS COUNT
FROM
  `dataset.W01_*`
GROUP BY
  Col1
ORDER BY
  COUNT DESC

Q#2:

SELECT
  DISTINCT Col1,
  COUNT(Col1) AS COUNT
FROM
  `dataset.W02_*`
GROUP BY
  Col1
ORDER BY
  COUNT DESC

With Results:

Result1     
Row Col1    COUNT
1   12345   33844
2   56789   32161
3   11223   31298

and

Result2     
Row Col1    COUNT
1   12345   33944
2   11223   41298
3   67890   12161

I want to combine the queries to get the following result:

Row Col1    COUNT
1   11223   41298
2   12345   33944
3   56789   32161
4   67890   12161

Basically in Result2:

1) 12345's count is bigger : 33944 than in Result1 and I want it with the new count in the new table.

2) Col1=67890 is new and I want it with its COUNT in the new table.

So the two results to be merged with updated new rows, new counts and sorted by COUNT in decreasing order.

Upvotes: 0

Views: 128

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Below is for BigQuery Standard SQL 

#standardSQL
WITH result1 AS (
  SELECT Col1, COUNT(Col1) AS count
  FROM `dataset.W01_*`
  GROUP BY Col1
), result2 AS (
  SELECT Col1, COUNT(Col1) AS count
  FROM `dataset.W02_*`
  GROUP BY Col1
)
SELECT col1, GREATEST(IFNULL(t1.count, t2.count), IFNULL(t2.count, t1.count)) count
FROM result1 t1 
FULL OUTER JOIN result2 t2
USING (col1)
ORDER BY count DESC

Also, note: you do not need DISTINCT in your Q1 and Q2 queries

Upvotes: 3

Related Questions