SQL Function COUNT explained
What is an aggregate function?
An aggregate function is a function that operates on the multiple rows in order to group them into a single value. Grouping simplifies data analysis.
Function COUNT () is used to check the group size or to count the rows of a table.
SELECTCOUNT(*) as totalCountFROM`bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
Above query returns the number of all records (NULLs included) in the table ga_sessions_20170801.
It is also possible to only count records that match our criteria.
SELECTCOUNT(*) as totalCountFROM`bigquery-public-data.google_analytics_sample.ga_sessions_20170801`WHERE device.browser = ‘Chrome’
In this example, we are only interested to count user sessions made with Chrome browser.
SELECTCOUNT(visitId) as totalVisitIdCountFROM`bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
The query above represents counting column values. Please note that when it comes to executing function COUNT() on columns, NULL values are ignored.
The DISTINCT keyword
Grouping functions can be also used to count unique values:
SELECTCOUNT(DISTINCT visitId) as uniqueVisitIdCountFROM`bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
If you place the DISTINCT keyword before the argument like on the above example, the query will return the number of users affected by at least one session.
The result of the query can also be interpreted as the number of unique users.
The DISTINCT keyword is part of the argument of the grouping function and is enclosed in parentheses for this reason. If you move it before the parentheses, the duplicate rows will be removed from the query result. It would not have sense in our example since the query returns only one row.