How to use IF Statement in BigQuery SQL
Problem: We have a Sales table in BigQuery and item_num column contains values 1, -1 and 0. You want to count how many cases you have for each value.
SELECT SUM(IF(item_num > 0, 1, 0)) AS buysplus, SUM(IF(item_num < 0, 1, 0)) AS buysminus, SUM(IF(item_num = 0, 1, 0)) AS buyszero FROM MyShop.Sales
or even less verbose version of it:
SELECT SUM(item_num > 0) AS buysplus, SUM(item_num < 0) AS buysminus, SUM(item_num = 0) AS buyszero FROM MyShop.Sales
this will give you result like below
buysplus buysminus buyszero 4 2 3
Another option would be a transposed version of it
SELECT item_num AS buys, COUNT(1) AS volume FROM MyShop.Sales GROUP BY 1
with the result as below
buys volume 0 3 1 4 -1 2
Read great educational content like this and a lot more !
Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too