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 NULLs.
Use ELSE null instead of ELSE 0
OR
use SUM() instead of count()