CODEWITHSUNDEEP
google-bigquery
Sema196
Sema196

Reputation: 263

Using IF in BigQuery SQL

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

Upvotes: 21

Views: 62336

Answers (2)

dinigo
dinigo

Reputation: 7448

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.

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

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

Upvotes: 29

Related Questions