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 18Enjoy great content like this and a lot more !
Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds
Post Comment