How to Use COUNTIF to Have Multiple Counts

BigQuery’s COUNTIF function makes it easy to perform conditional counts inside your SQL queries — and with a little technique, you can perform multiple counts in one query for streamlined analysis.

What is COUNTIF?

COUNTIF is an aggregate function in BigQuery that counts rows that meet a specific condition. It’s commonly used to get counts of rows matching criteria like a particular status, category, or date range.

SELECT 
  COUNTIF(status = 'active') AS active_count,
  COUNTIF(status = 'inactive') AS inactive_count
FROM users;

Why Use Multiple COUNTIFs?

Instead of writing multiple queries or using complex CASE WHEN statements, you can stack multiple COUNTIF expressions in a single SELECT to get several counts at once. This approach is especially useful in dashboards or summary reports.

Example: Counting Multiple Conditions

Imagine you have an orders table with a status column. You want to know how many orders are:

• Completed
• Pending
• Cancelled

You can do this with one query:

SELECT
  COUNTIF(status = 'completed') AS completed_orders,
  COUNTIF(status = 'pending') AS pending_orders,
  COUNTIF(status = 'cancelled') AS cancelled_orders
FROM orders;

Example with Additional Grouping

You can also combine COUNTIF with GROUP BY to break down counts by another dimension, such as region:

SELECT
  region,
  COUNTIF(status = 'completed') AS completed_orders,
  COUNTIF(status = 'pending') AS pending_orders,
  COUNTIF(status = 'cancelled') AS cancelled_orders
FROM orders
GROUP BY region;

Tips and Best Practices

• Use clear aliases: Give each COUNTIF a descriptive alias to keep your results easy to read.
• Combine with WHERE as needed: You can still apply overall filters using the WHERE clause.
• Test your conditions: Double-check the logic inside each COUNTIF to avoid missing counts or miscounting rows.

Conclusion

Using multiple COUNTIF expressions in BigQuery lets you efficiently summarize data across several conditions in one query. This saves time, reduces complexity, and improves the clarity of your reports and dashboards.

Now that you know this technique, try it out in your next BigQuery project!