I want to make a summation on a column using information on two columns

Question

I have the following simplified table in sql server:

PartyID LicensePlate    Amount
123 XVE158          10
123 VSE587          20
128 XVE158          30
128 VES874          80
158 TYU684          20
158 VTI267          60
356 VES874          30
356 BNU673          20
356 AAH637          60

Now I want to make a summation of the column Amount with every PartyID that has a common LicensePlate. PartyID 123 and 128 have LicensePlate XVE158 in common. PartyID 128 and 356 have LicensePlate VES874 in common. PartyID 123 and 356 have no LicensePlate in common but I still want them in the same group as they are linked togetter with PartyId 128. As an end result I would like to have a column that gives back all the distinct LicensePlates with this logic with the total amount. So for this example that would be:

LicensePlates                               Total Amount
XVE158, VSE587, VES874, BNU673, AAH637      250
TYU684, VTI267                              80

I have never tried to do this sort of a summation before so I have no idea where to start.

@ThomA I guess it's hard to find if you don't know `STRING_AGG` exists - it took me quite some time to find the duplicate. — Filburt, Aug 11 '23 at 14:09
There's a lot of searches that lead me another [canonical](https://stackoverflow.com/questions/18870326/comma-separated-results-in-sql) in my prefered search engine, @Filburt . Getting delimited results, and consuming them, are one of the most asked questions on [so] for [[tag:sql-server]]; there's a lot of dupes. — Thom A, Aug 11 '23 at 14:12
with STRING_AGG I can concatenate all the license plates of 1 PartyID but in my example I need to concatenate an sum multiple PartyID's which have a common LicensePlate so I don't see the solution with STRING_AGG. Can you give me the code that would give me the desired result? — Axel244, Aug 11 '23 at 14:28
`STRING_AGG` will just *aggregate* your strings; you need to work out how to assign those groups. — Thom A, Aug 11 '23 at 14:31

score 1 · Accepted Answer · answered Aug 11 '23 at 15:15

I think you're going to need some recursive common table expression (rCTE) magic for this, unless the number of relationships is finite (Each Party can only have n relationships?)

Here's the inferred DDL/DML (it helps a lot if you provide this with your questions)

DECLARE @Plates TABLE (PartyID BIGINT, LicensePlate NVARCHAR(10), Amount INT)
INSERT INTO @Plates (PartyID, LicensePlate, Amount) VALUES
(123, 'XVE158', 10), (123, 'VSE587', 20), (128, 'XVE158', 30), (128, 'VES874', 80), 
(158, 'TYU684', 20), (158, 'VTI267', 60), (356, 'VES874', 30), (356, 'BNU673', 20),
(356, 'AAH637', 60);

First we need to establish the relationships, we can do that with a self join where the plate is the same, but the party is not. In order to prevent getting bidirectional matches, I used a less than to get only the lower party on the left side.

Then we need to figure out our base 'parent' rows, the ones we're ultimately going to group on.

Finally, we can then recurse our way through those relationships, starting with the roots to tie each row to a base parent party. (123 or 158 in this case).

Now we're ready to aggregate the data based on this, using the STRING_AGG and SUM functions.

;WITH CommonPlates AS (
SELECT p.PartyID AS PartyA, p2.PartyID AS PartyB
  FROM @Plates p
    INNER JOIN @plates p2
      ON p.LicensePlate = p2.LicensePlate
      AND p.PartyID < p2.PartyID
), PartyTime AS (
SELECT p.PartyID AS BaseParty, p.PartyID AS ThisParty, c.PartyB AS NextParty
  FROM @Plates p
    LEFT OUTER JOIN CommonPlates c
      ON p.PartyID = c.PartyA
    LEFT OUTER JOIN CommonPlates cp
      ON p.PartyID = cp.PartyB
 WHERE cp.PartyA IS NULL
GROUP BY p.PartyID, c.PartyB
UNION ALL
SELECT a.BaseParty, r.PartyA, r.PartyB
  FROM PartyTime a
    INNER JOIN CommonPlates r
      ON a.NextParty = r.PartyA
)

SELECT STRING_AGG(LicensePlate, ', ') AS LicensePlates, SUM(Amount) AS TotalAmount, COALESCE(pt.BaseParty,p.PartyID)
  FROM @Plates p
    LEFT OUTER JOIN PartyTime pt
      ON p.PartyID = NextParty
 GROUP BY COALESCE(pt.BaseParty,p.PartyID)

LicensePlates	TotalAmount	BaseParty
XVE158, VSE587, XVE158, VES874, VES874, BNU673, AAH637	250	123
TYU684, VTI267	80	158

I want to make a summation on a column using information on two columns

1 Answers1