How to Use countif to Have Multiple Counts in BigQuery

Say you want to do multiple counts on a table in the same query. In BigQuery you can use the countif function. This approach is very similar to the filter command in Postgres.

  count(1), -- all posts
  countif(type = 'story'), -- all stories
  countif(type = 'comment'), -- all comments on existing story
  countif(type = 'poll'), -- all polls
  countif(type = 'job') -- all jobs posted
from `bigquery-public-data.hacker_news.full`

In case you want to try this at home, we're using a BigQuery public dataset on Hacker News in our example above.

The results as of this writing:

hacker news posts by type

You can get started with BigQuery in PopSQL in less than 5 minutes. Exploring BigQuery is a joy in PopSQL, a modern editor built for teams that supports all major databases and operating systems. 😍

database icon
SQL editing that just rocks
PopSQL and BigQuery, better together