Search code examples
sqlhivehiveql

COUNT with CASE WHEN is showing the same result when using division

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.

Solution

  • count() counts both 0 and 1s, it does not counts NULLs.

    Use ELSE null instead of ELSE 0

    OR

    use SUM() instead of count()