Using IF in BigQuery SQL

Question

Case: I have Sales table in BQ and item_num column contains values 1, -1 and 0. I want to count how many cases I have for each value.

Tried a simple query below, but count returns exactly the same number for each case.. What I am missing?

SELECT 
count(if(item_num > 0,1, 0)) as buysplus,
count(if(item_num < 0,1, 0)) as buysminus,
count(if(item_num = 0,1, 0)) as buyszero
from MyShop.Sales

thanks

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

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 yo result like below

buysplus    buysminus   buyszero     
4           2           3

Another option would be 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    

Answer 2

I would use COUNTIF, just like @justbeez sugests. According to documentation

Returns the count of TRUE values for expression

So applied to your case would look like:

SELECT 
  COUNTIF(item_num > 0) as buysplus,
  COUNTIF(item_num < 0) as buysminus,
  COUNTIF(item_num = 0) as buyszero
FROM MyShop.Sales

You don't save a lot of code, but count is a little bit more idiomatic than sum IMHO.