Site icon Tech Express Hub

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

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

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
Exit mobile version