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

Steffy Alen

3 years ago

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