BigQuery - count the count of a column

Question

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

At present in your query WhitelistNames represents an alias of the count of appname, what else do you want to count? — jimmy8ball
– jimmy8ball, Commented Apr 27, 2018 at 19:02
I wrote more details in the description to explain the desired results better. Thanks. — mzichao
– mzichao, Commented Apr 27, 2018 at 20:25

Mikhail Berlyant · Accepted Answer · 2018-04-27 20:53:42Z

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

Excuse me for the delayed response. I've edited my question to make it more clear. Thanks in advance.

Temu · Accepted Answer · 2018-05-25 10:34:49Z

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

Collectives™ on Stack Overflow

BigQuery - count the count of a column

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related