Difficult average combination in MS SQL Server? [duplicate]
One option, aggregating first by month and year, then by product:
WITH cte AS (
SELECT PRODUCT, 1.0*COUNT(*) AS cnt
FROM yourTable
GROUP BY PRODUCT, FORMAT(DATE, 'MM.yyyy')
)
SELECT PRODUCT, AVG(cnt) AS AVG_PER_MNTH
FROM cte
GROUP BY PRODUCT;
Demo
WITH CTE AS
(SELECT PRODUCT
,COUNT(*) ac cnt,
,DATEADD(month, DATEDIFF(month, 0, Date), 0) AS StartOfMonth
FROM YourTable
GROUP BY PRODUCT
,DATEADD(month, DATEDIFF(month, 0, Date), 0)
)
SELECT
PRODUCT
,AVG(cnt) as MonthlyAverage
FROM CTE
GROUP BY PRODUCT