By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t
(Location varchar(4), Type varchar(9), PrimaryLocation varchar(4), Usage int)
;
INSERT INTO t
([Location], [Type], [PrimaryLocation], [Usage])
VALUES
('01', 'Primary', NULL, 1),
('02', 'Secondary', '01', 1),
('03', 'Secondary', '01', 1),
('04', 'Normal', NULL, 1)
;
4 rows affected
select t.*,
(case when primarylocation is null
then sum(usage) over (partition by coalesce(primarylocation, location))
end) as trueusage
from t;
| Location | Type | PrimaryLocation | Usage | trueusage |
|---|---|---|---|---|
| 01 | Primary | null | 1 | 3 |
| 02 | Secondary | 01 | 1 | null |
| 03 | Secondary | 01 | 1 | null |
| 04 | Normal | null | 1 | 1 |