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 
  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:

Solution 2:

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

Solution 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    

victor

posted on 08 Aug 18