CODEWITHSUNDEEP
google-bigquery
mjdxb
mjdxb

Reputation: 85

How to get multiple counts on different conditions in same query in BigQuery?

I want to get multiple counts in the same query but with different conditions.

In theory, Engaged+NonEngaged will be equal to TotalCounts in my scenario

Upvotes: 1

Views: 3651

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173003

Below is for BigQuery Standard SQL (make sure you preserve first row in below script which set Standard SQL to be used even if you don't explicitly set this in UI or whatever tool, library, api you use) 

#standardSQL
SELECT 
  COUNT(DISTINCT columnA) AS TotalCounts,
  COUNT(DISTINCT IF(flag , columnA, NULL)) AS Engaged,
  COUNT(DISTINCT IF(NOT flag , columnA, NULL)) AS NonEngaged
FROM (  
  SELECT columnA, SUM(columnB + ColumnC) > 0 AS flag
  FROM `project.dataset.table`
  GROUP BY columnA
)

Update to address extra question in comments - what if we need to get counts from two separate tables in the same query? let's say the 4th column in the same query above should get the distinct count of columnY from tableXYZ

The simplest option is to do as below. Cross Join is OK here because each result has just one row 

#standardSQL
SELECT 
  TotalCounts,
  Engaged,
  NonEngaged,
  distinctY
FROM (
  SELECT 
    COUNT(DISTINCT columnA) AS TotalCounts,
    COUNT(DISTINCT IF(flag , columnA, NULL)) AS Engaged,
    COUNT(DISTINCT IF(NOT flag , columnA, NULL)) AS NonEngaged 
  FROM (  
    SELECT columnA, SUM(columnB + ColumnC) > 0 AS flag
    FROM `project.dataset.table`
    GROUP BY columnA
  )
)
CROSS JOIN (
  SELECT COUNT(DISTINCT columnY) distinctY
  FROM `project.dataset.tableXYZ`
)

Upvotes: 4

Related Questions