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