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
| 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.