CODEWITHSUNDEEP
google-bigquery
mzichao
mzichao

Reputation: 3

BigQuery - count the count of a column

Newbie on SQL and BigQuery in general. How to count the count of a column in BigQuery? As you can see from the code sample, the query returns the count of appName as WhitelistNames, but I would like to get a count of WhitelistNames.

SELECT
COUNT(appName) AS WhitelistNames,
  bridgeToken
FROM (
  SELECT
    bridgeToken,
    appName
  FROM
    [DB]
  GROUP BY
    bridgeToken,
    appName )
GROUP BY
  bridgeToken
ORDER BY
  WhitelistNames DESC

Current query return is:

Row UniquebridgeToken   WhitelistEntries    
1   11111               5         
2   22222               13    
3   33333               3     
4   44444               3
5   55555               3

But I would like to count the occurrence of UniquebridgeToken like below. Thanks in advance.:

Row WhitelistEntries    BridgeCount
1   13                  1
2   5                   1
3   3                   3

Upvotes: 0

Views: 3431

Answers (2)

Temu
Temu

Reputation: 879

I understand that you want is to count how many UniquebridgeToken have the same number of WhitelistEntries. I think what you are looking for is that: 

WITH nestedQuery AS (SELECT
    appName,
    COUNT(appName) as WhitelistEntries
FROM `project_name.dataset_name.table_name`
GROUP BY 
     price)

SELECT n.WhitelistEntries, COUNT(n.WhitelistEntries) as BridgeCount
FROM nestedQuery as n
GROUP BY n.WhitelistEntries

You can read about WITH clause here: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#with_clause

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below is for BigQuery Standard SQL and based on how I interpreted your question - which is:
for each bridgeToken how many unique appName's and how many total entries (rows) for that bridge

#standardSQL
SELECT 
  COUNT(DISTINCT appName) AS WhitelistNames, 
  COUNT(bridgeToken) AS BridgeCount
FROM `project.dataset.your_table`
GROUP BY bridgeToken

Upvotes: 2

Related Questions