How do you calculate percentage within window function respecting joins and boolean condition?-CodePudding

Sales Table

Sale_ID int

Sale_approved_date date

Store_ID int

Store Table

Store_ID int

Store_Name varchar

Store_Special boolean

I added the below code to calculate Store_Special with respect to overall store_special where the year is 2018. I can't seem to do a calculation where I need two group by to extract the condition with respect to overall where there is no condition for st.store_special = 1.

Expected Outcome: dd.store_special = 1/ dd.store_special =1 or dd.store_special = 0 (overall store_special count) ==> Group by month to see the ratio of store special = 1 / no condition/total count without condition.

SELECT    Datepart(month,s.sale_approved_date) AS month,
          Count(Datepart(month,s.sale_approved_date))/ (Sum((Datepart(month,s.sale_approved_date)) ) 
from      sales s
LEFT JOIN store AS st
ON        s.store_id = st.store_id 
WHERE     datepart(year,.sale_approved_date) = '2018'
AND       dd.store_special = 1 
GROUP BY  datepart(month,.sale_approved_date) 
ORDER BY  count(*) DESC

CodePudding user response：

you can use select case statement to achieve this. This is the ratio of store_special = true and overall count

SELECT    Datepart(month,s.sale_approved_date) AS month,
          sum(case when dd.store_special = 1 then 1 else 0 end)/count() 
from      sales s
LEFT JOIN store AS st
ON        s.store_id = st.store_id 
WHERE     datepart(year, s.sale_approved_date) = '2018' 
GROUP BY  datepart(month, s.sale_approved_date) 
ORDER BY  count(*) DESC

CodePudding user response：

Once you get the counts, you need to make sure to convert either the numerator or denominator to a float type (*1.0) to get the expected ratio. Otherwise, the ratios will be zero.

SELECT MONTH(S.sale_approved_date) AS Month,
       COUNT(CASE WHEN ST.store_special = 1 THEN 1 END)*1.0 /COUNT(*) AS Ratio
FROM Store ST
JOIN Sales S
  ON ST.Store_ID = S.Store_ID
WHERE YEAR(S.sale_approved_date) = 2018 
GROUP BY MONTH(S.sale_approved_date) 
ORDER BY COUNT(*) DESC