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.

Solution 1:

SELECT \n  SUM(IF(item_num > 0, 1, 0)) AS buysplus,\n  SUM(IF(item_num < 0, 1, 0)) AS buysminus,\n  SUM(IF(item_num = 0, 1, 0)) AS buyszero\nFROM MyShop.Sales\n

or even less verbose version of it:

Solution 2:

SELECT \n  SUM(item_num > 0) AS buysplus,\n  SUM(item_num < 0) AS buysminus,\n  SUM(item_num = 0) AS buyszero\nFROM MyShop.Sales\n

this will give you result like below

buysplus    buysminus   buyszero     \n4           2           3\n

Solution 3:

Another option would be a transposed version of it

SELECT \n  item_num AS buys,\n  COUNT(1) AS volume\nFROM MyShop.Sales\nGROUP BY 1  \n

with the result as below

 buys        volume  \n 0          3    \n 1          4    \n-1          2    \n

victor

posted on