Geek Answers Handbook

Using IF in BigQuery SQL

Case : I have a sales table in the BQ columns and item_num contains the values ​​1, -1 and 0. I want to calculate how many cases I have for each value.

Tried a simple query below, but count returns exactly the same number for each case. What am I 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

+6
source share
1 answer
 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 verbal version:

 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 as below

 buysplus buysminus buyszero 4 2 3

Another option is a transposed version

 SELECT item_num AS buys, COUNT(1) AS volume FROM MyShop.Sales GROUP BY 1

with the result below

  buys volume 0 3 1 4 -1 2
+8
source

More articles:


All Articles