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.
CodePudding user response:
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()