Weighted Average to combine data into one row-CodePudding

I am trying to achieve weighted average data in single row. This is what my current data look like

Name	Position	Id	score1	score2	list	lastName
ABC	POS1	12345	10	20	2	YUI
ABC	POS2	12345	20	10	5	YUI
ABC	POS3	12345	20	30	7	YUI
ABC	POS4	12345	10	50	2	YUI
XYZ	POS1	67890	5	10	5	OPR
XYZ	POS2	67890	30	20	3	OPR
XYZ	POS3	67890	40	40	1	OPR
XYZ	POS4	67890	20	10	2	OPR

Desire output should be like below

Name	Position	Id	score1	score2	list	lastName
ABC	POS1, POS2, PO3, POS4	12345	17.5	25	16	YUI
XYZ	POS1, POS2, PO3, POS4	67890	17.72	15.45	11	OPR

Score1 is calculated as sum(score1 * list/ sum(list)) based on id

For ABC (ID 12345) Score1= 10*(2/16) 20*(5/16) 20*(7/16) 10*(2/16) = 17.5

Score2= 20*(2/16) 10*(5/16) 30*(7/16) 50*(2/16) = 25

List = 2 5 7 2 = 16

For XYZ(ID 67890) Score1= 5*(5/11) 30*(3/11) 40*(1/11) 20*(2/11) = 17.72

Score2= 10*(5/11) 20*(3/11) 40*(1/11) 10*(2/11) = 15.45

List = 5 3 1 2 = 11

Position data are string aggregate of different positions. Position data are not same always, it could be 4 rows for one and 2 rows for other.

Any help would be really great, thank you!

CodePudding user response：

On SQL Server 2017 or greater:

SELECT 
  Name, 
  Position = STRING_AGG(Position, ', '), 
  Id,  
  Score1 = CONVERT(DECIMAL(18,2), SUM(Score1 * 1.0 * list) / SUM(list)), 
  Score2 = CONVERT(DECIMAL(18,2), SUM(Score2 * 1.0 * list) / SUM(list)),
  list = SUM(list), 
  lastName
FROM dbo.Scores
GROUP BY Name, Id, lastName;

Output:

Name	Position	Id	Score1	Score2	list	lastName
ABC	POS1, POS2, POS3, POS4	12345	17.50	25.00	16	YUI
XYZ	POS1, POS2, POS3, POS4	67890	17.73	15.45	11	OPR

On older versions, same results, just uglier and less efficient:

SELECT 
  Name, 
  Position = STUFF((SELECT ', '   Position
    FROM dbo.Scores
    WHERE Id = s.Id
    FOR XML PATH(''), TYPE).value(N'./text()[1]',
                            N'varchar(max)'),1,1,''),
  Id,  
  Score1 = CONVERT(DECIMAL(18,2), SUM(Score1 * 1.0 * list) / SUM(list)), 
  Score2 = CONVERT(DECIMAL(18,2), SUM(Score2 * 1.0 * list) / SUM(list)),
  list = SUM(list), 
  lastName
FROM dbo.Scores AS s
GROUP BY Name, Id, lastName;

Example db<>fiddle

CodePudding user response：

you could use a join with a sub query for sum list

select a.name, STRING_AGG(a.Position, ','), a.id,   sum(a.score1*a.list)/t.tot_x_list, sum(a.score2*(a.list/t.tot_x_list))
from my_table a
inner join (
    select id, sum(list) tot_x_list 
    from my_table 
    group by id
) t on t.id = a.id 
group by a.id