By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [OWNER](
[personId] [int] NULL,
[ownerId] [int] NULL,
[firstName] [varchar](255) NULL,
[lastName] [varchar](255) NULL,
);
CREATE TABLE [INCOME](
[personId] [int] NULL,
[amount] [float] NULL,
[received] [date] NULL,
)
INSERT INTO OWNER Values(1,null, 'John', 'Smith')
INSERT INTO OWNER Values(1,null, 'John', 'Smith')
INSERT INTO OWNER Values(1,null, 'John', 'Smith')
INSERT INTO OWNER Values(200,1, 'Tom', 'Lawn')
INSERT INTO OWNER Values(3,3, 'Chris', 'Hanson')
INSERT INTO OWNER Values(400,4, 'Jack', 'Man')
INSERT INTO OWNER Values(4,null, 'Donald', 'McMan')
INSERT INTO OWNER Values(5,null, 'John', 'Hanson')
INSERT INTO OWNER Values(700,5, 'Peter', 'Darcy')
INSERT INTO OWNER Values(700,5, 'Peter', 'Darcy')
Insert INTO INCOME VALUES(1, 300, '2020-01-01')
Insert INTO INCOME VALUES(200, 1000, '2020-01-01')
Insert INTO INCOME VALUES(3, 200, '2020-01-01')
Insert INTO INCOME VALUES(4,300, '2020-01-01')
Insert INTO INCOME VALUES(5,300, '2020-01-01')
Insert INTO INCOME VALUES(1,300, '2020-01-01')
Insert INTO INCOME VALUES(3,300, '2020-01-01')
Insert INTO INCOME VALUES(5,500, '2020-01-01')
Insert INTO INCOME VALUES(700,500, '2020-01-01')
19 rows affected
select coalesce(o.ownerid, i.personid),
sum(amount) as total,
count(*) as rows
from income i cross apply
(select top (1) o.*
from owner o
where o.personid = i.personid
) o
group by coalesce(o.ownerid, i.personid)
| (No column name) | total | rows |
|---|---|---|
| 1 | 1600 | 3 |
| 3 | 500 | 2 |
| 4 | 300 | 1 |
| 5 | 1300 | 3 |