I have the following query which returns the sold products:
SELECT COUNT(CASE WHEN sold_flag =1 AND product_category_ = 7 AND year(sold_date) = 2021 THEN 1 ELSE 0 END) FROM fact_sales;
I want to calculate the percentage of the sold products comparing to all products for product_category =7 :
SELECT (COUNT(CASE WHEN sold_flag =1 AND product_category_ = 7 AND year(sold_date) = 2021 THEN 1 ELSE 0 END) / COUNT(CASE WHEN product_category_ = 7 AND year(sold_date) = 2021 THEN 1 ELSE 0 END)) * 100 FROM fact_sales;
I get the result as 100, while I execute each query separately they don’t have the same result.
Advertisement
Answer
count()
counts both 0 and 1s, it does not counts NULL
s.
Use ELSE null
instead of ELSE 0
OR
use SUM()
instead of count()