COUNT function in BigQuery

The COUNT function in BigQuery is used to count the number of rows in a table.

Note: The COUNT function returns the count of non-null values in a specified column; it ignores the null values.

It is often used in conjunction with the SELECT statement to retrieve aggregated data.

SQL Syntax

SELECT COUNT(column_name)
FROM `project_name.dataset_name.table_name`
WHERE condition;    

Example: Let’s have the following employees table in BigQuery.

By executing the following query, we can see all the rows from the table.

SQL

SELECT * FROM `ashishcoder.Coding_Dataset.employees`;  

The output of the above code is shown below:

Count Total Number of Employees

To count the total number of employees in the employees table:

SQL

SELECT COUNT(*) AS total_employees
FROM `ashishcoder.Coding_Dataset.employees`;    

The output of the above code is shown below:

Count Distinct Values in a Column

To count the number of distinct departments:

SQL

SELECT COUNT(DISTINCT department) AS distinct_departments
FROM `ashishcoder.Coding_Dataset.employees`; 

The output of the above code is shown below:

This query counts the number of unique values in the department column, which are Electrical, Electronics, and Computer.