SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DROP TABLE IF EXISTS #X

SELECT *
INTO #X
FROM (
	SELECT 1 as Id, 1 as Location
	UNION
	Select 1 as Id, NULL as Location
	UNION
	Select 2 as Id, NULL as Location
	UNION
	Select 3 as Id, 2 as Location
) X

-- This is what I wish I could do but its wrong
SELECT Id, Location, COUNT(*) as Count, COUNT(Location) as LocationCount
FROM #X
GROUP BY Id, Location
ORDER BY Id, Location

-- This is the what I am after but cant use AVG in a indexed view
SELECT Id, AVG(Location) as Location, COUNT(*) as Count, COUNT(Location) as LocationCount
FROM #X
GROUP BY Id
ORDER BY Id, Location

;WITH IndexableViewNonNulls AS (
	SELECT Id, Location, COUNT(*) as Count
	FROM #X
	WHERE Location IS NOT NULL
	GROUP BY Id, Location
), IndexableViewNulls AS (
	SELECT Id, Location, COUNT(*) as Count
	FROM #X
	WHERE Location IS NULL
	GROUP BY Id, Location
), CTE AS (
	SELECT *, Count as LocationCount
	FROM IndexableViewNonNulls
	UNION ALL
	SELECT *, 0 as LocationCount
	FROM IndexableViewNulls
)

SELECT 
	  Id
      ,AVG(Location) as Location
      ,SUM(Count) as Count
	  ,SUM(LocationCount) as LocationCount
FROM CTE
GROUP BY Id

Id	Location	Count	LocationCount
1	null	1	0
1	1	1	1
2	null	1	0
3	2	1	1

Warning: Null value is eliminated by an aggregate or other SET operation.

Id	Location	Count	LocationCount
1	1	2	1
2	null	1	0
3	2	1	1

Warning: Null value is eliminated by an aggregate or other SET operation.

Id	Location	Count	LocationCount
1	1	2	1
2	null	1	0
3	2	1	1

Warning: Null value is eliminated by an aggregate or other SET operation.