By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
declare @t1 as table
(
sn int,
site varchar(max),
cond varchar(max),
val int
)
insert into @t1
select *
from
(values
(1, 'site1', 'X', 100),
(2, 'site1', 'Y', 200),
(3, 'site1', 'Z', 300),
(1, 'site2', 'A', 100),
(2, 'site2', 'B', 200),
(3, 'site2', 'C', 300),
(1, 'site3', 'X', 100),
(2, 'site3', 'P', 200),
(3, 'site3', 'Q', 300),
(1, 'site4', 'A', 100),
(2, 'site4', 'Y', 200),
(3, 'site4', 'Q', 300),
(1, 'site5', 'E', 100),
(1, 'site5', 'E', 1000),
(2, 'site5', 'F', 200),
(3, 'site5', 'G', 300)
) t (a, b, c, d);
select
ax.sn, ax.site, ax.cond,
sum(ax.revisedVal) as revisedTotal
from
(select distinct
| sn | site | cond | revisedTotal |
|---|---|---|---|
| 1 | site1 | X | null |
| 2 | site1 | Y | null |
| 3 | site1 | Z | null |
| 1 | site2 | A | 100 |
| 2 | site2 | B | 200 |
| 3 | site2 | C | 300 |
| 1 | site3 | X | null |
| 2 | site3 | P | null |
| 3 | site3 | Q | null |
| 1 | site4 | A | null |
| 2 | site4 | Y | null |
| 3 | site4 | Q | null |
| 1 | site5 | E | 1100 |
| 2 | site5 | F | 200 |
| 3 | site5 | G | 300 |
Warning: Null value is eliminated by an aggregate or other SET operation.
| sn | site | cond | revisedTotal |
|---|---|---|---|
| 1 | site1 | X | null |
| 2 | site1 | Y | null |
| 3 | site1 | Z | null |
| 1 | site2 | A | 100 |
| 2 | site2 | B | 200 |
| 3 | site2 | C | 300 |
| 1 | site3 | X | null |
| 2 | site3 | P | null |
| 3 | site3 | Q | null |
| 1 | site4 | A | null |
| 2 | site4 | Y | null |
| 3 | site4 | Q | null |
| 1 | site5 | E | 1100 |
| 2 | site5 | F | 200 |
| 3 | site5 | G | 300 |
Warning: Null value is eliminated by an aggregate or other SET operation.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) Jul 25 2020 11:26:55 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |