01_aggregateFunctions.sql

-- Aggregate functions
SELECT *,
	AVG(t1.Value) 
		OVER(
		PARTITION BY t1.Area, t1.Property_type
		ORDER BY t1.Date
		) avgValue,
	COUNT(*)
		OVER(
		PARTITION BY t1.Area, t1.Property_type
		ORDER BY t1.Date
		) vol,
	MIN(t1.Value)
		OVER(
		PARTITION BY t1.Area, t1.Property_type
		ORDER BY t1.Date
		) minValue,
	MAX(t1.Value)
		OVER(
		PARTITION BY t1.Area, t1.Property_type
		ORDER BY t1.Date
		) maxValue
FROM Medium.dbo.HPM09_202112 AS t1
WHERE t1.Value > 0
	AND t1.Date BETWEEN '2020-01-01' AND '2020-06-01'