BigQuery - count the count of a column

0
votes

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
2
google-bigquery
At present in your query WhitelistNames represents an alias of the count of appname, what else do you want to count? – jimmy8ball
I wrote more details in the description to explain the desired results better. Thanks. – mzichao

2 Answers

2
votes

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
0
votes

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