Azure SQL: Group a result based on a sub select statement

I have an issue with a query where I want to accumulate values and then group them PS: This is a simplified example

CREATE TABLE [dbo].[OWNER](
    [personId] [int] NULL,
    [ownerId] [int] NULL,
    [firstName] [varchar](255) NULL,
    [lastName] [varchar](255) NULL,
)
GO

CREATE TABLE [dbo].[INCOME](
    [personId] [int] NULL,
    [amount] [float] NULL,
    [received] [date] NULL,
)
GO


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')

In the Owner table there can be duplicate records. To do an aggregate I can write

select personId, 
       sum(amount) as total,
       count(*) as rows
from income
group by personid

This will result in

personId    total   rows
1           600      2
3           500      2
4           300      1
5           800      2
200         1000     1
700         500      1

The problem is that I want to get the aggregate on Owner and get the following result

personId    total  rows
1           1600   3
3           500    2
4           300    1
5           1300   3

Since ownerId for personId 200 is 1 and ownerId for personId 700 is 5. I would appreciate help on how this can be achieved.

Answer

You have a real problem with your data model — and you should fix that. Having duplicate rows in owner is just bad. At the very least, you could have an effective date, so you know what the most recent record is

That said, you can use apply to choose an arbitrary matching record and use that:

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);

Here is a db<>fiddle.

Advertisement

Answer