SQL Query with group by and update

Question

SQL Query with group by and update

Spunny 366

Hi,

I have temp table that has data like this:

DECLARE @tmp1 TABLE

(

	Amount money, 		

	sip varchar(12),

	ShortName varchar(20),

	Fund varchar(8),

	SettleDate varchar(50),

	TrnType varchar(12),

	SecDesc varchar(100),

	Par decimal(14,0),

	Price decimal(14,8),

	AInterest decimal(14,2),

	SecType varchar(50) NULL,

	Loc varchar(3),	

	code varchar(4),

	ProductType varchar(25),

	TransNum varchar(20),		

	Tdate varchar(50),

	PKey int

	

)

DECLARE @tmp2 TABLE

(

	Amount money, 		

	sip varchar(12),

	ShortName varchar(20),

	Fund varchar(8),

	SettleDate varchar(50),

	TrnType varchar(12),

	SecDesc varchar(100),

	Par decimal(14,0),

	Price decimal(14,8),

	AInterest decimal(14,2),

	SecType varchar(50) NULL,

	Loc varchar(3),	

	code varchar(4),

	ProductType varchar(25),

	TransNum varchar(20),		

	Tdate varchar(50),

	PKey int

)

INSERT INTO @tmp1

SELECT 4986474.00, '16677JX27', 'BIDL', '100', '20250327', 'B', 'CHEVRON CORP', 5100000, 97.77400000, 0.00, 'CP', 'DTC', 'OA', 'Money Market', 863233, '20250327', 6

UNION

SELECT 97774000.00, '16677JX27', 'BIDL', '100', '20250327', 'B', 'CHEVRON CORP', 100000000, 97.77400000, 0.00, 'CP', 'DTC', 'OA', 'Money Market', 863137, '20250326', 6

UNION

SELECT 24925000.00, '93114ERM1', 'MIZUHOSEC', '100', '20250327', 'B', 'WALMART INC', 25000000, 99.70000000, 0.00, 'CP', 'DTC', 'OA', 'Money Market', 863241, '20250327', 6

UNION

SELECT 74775260.42, '93114ERM1', 'BARCLAYS', '100', '20250327', 'B', 'WALMART INC', 75000000, 99.70034722, 0.00, 'CP', 'DTC', 'OA', 'Money Market', 863237, '20250327', 6

select * from @tmp1

INSERT INTO @tmp2 (Amount, sip, Fund, ShortName)

SELECT SUM(Amount), sip, fund, ShortName

FROM @tmp1

GROUP BY sip, Fund, ShortName

SELECT * FROM @tmp2

--I am trying to update rest of the fields like settledate, trntype from tmp1 to tmp2 based on sip, amount and shortname. but in tmp1 there is no same amount for sip 16677JX27. How should I do the update. tried this

UPDATE cpe

SET 	cpe.SettleDate = tce.SettleDate,

		cpe.TrnType = tce.TrnType,

		cpe.SecDesc = tce.SecDesc,

		cpe.Par = tce.Par,

		cpe.Price = tce.Price,

		cpe.AInterest = tce.AInterest,

		cpe.SecType = tce.SecType,

		cpe.Loc = tce.Loc,

		cpe.code = tce.code,

		cpe.ProductType = tce.ProductType,

		cpe.TransNum = tce.TransNum,

		cpe.Tdate = tce.TDate,

		cpe.PKey = tce.PKey

		FROM @tmp1 tce INNER JOIN @tmp2 cpe ON tce.SIP = cpe.SIP AND tce.Amount = cpe.Amount 

					AND (ISNULL(tce.shortName, '') = ISNULL(cpe.ShortName, ''))

SELECT * from @tmp2

Notice sip 16677JX27 fields are not updated. it is sum of both transactions. How do I get data for this to be updated

please help.
Thank You

Viorel 121.3K Reputation points

2025-03-28T01:49:37.73+00:00

What result did you decide to achieve?
Spunny 366 Reputation points

2025-03-28T13:46:16.87+00:00

Hi Viorel, thanks for looking into it.

Expected below result set

But getting below result

first row for 16677JX27 has rest of the data NULL.

Viorel 121.3K

Maybe like this:

;
with Q1 as
(
    select sum(Amount) as SumAmount, sip, Fund, isnull(ShortName, '') as ShortName, max(TransNum) as MaxTransNum
    from @tmp1
    group by sip, Fund, isnull(ShortName, '')
),
Q2 as
(
    select Q1.SumAmount, tce.*
    from @tmp1 tce
    inner join Q1 on Q1.sip = tce.sip and Q1.Fund = tce.Fund and Q1.ShortName = isnull(tce.ShortName, '') and Q1.MaxTransNum = tce.TransNum
)
update cpe
set cpe.SettleDate = tce.SettleDate,
    cpe.TrnType = tce.TrnType,
    cpe.SecDesc = tce.SecDesc,
    cpe.Par = tce.Par,
    cpe.Price = tce.Price,
    cpe.AInterest = tce.AInterest,
    cpe.SecType = tce.SecType,
    cpe.Loc = tce.Loc,
    cpe.code = tce.code,
    cpe.ProductType = tce.ProductType,
    cpe.TransNum = tce.TransNum,
    cpe.Tdate = tce.TDate,
    cpe.PKey = tce.PKey
from @tmp2 cpe 
inner join Q2 tce on tce.SIP = cpe.SIP and tce.SumAmount = cpe.Amount and tce.shortName = isnull(cpe.ShortName, '')

Spunny 366 Reputation points

2025-03-28T21:27:31.58+00:00

Hi Viorel,

thank you. your suggestion helped. it worked.

1 answer

Your answer

Viorel 121.3K Reputation points

2025-03-28T01:49:37.73+00:00

What result did you decide to achieve?
Spunny 366 Reputation points

2025-03-28T13:46:16.87+00:00

Hi Viorel, thanks for looking into it.

Expected below result set

But getting below result

first row for 16677JX27 has rest of the data NULL.
Viorel 121.3K Reputation points

2025-03-28T15:42:55.35+00:00

Maybe like this:

; with Q1 as ( select sum(Amount) as SumAmount, sip, Fund, isnull(ShortName, '') as ShortName, max(TransNum) as MaxTransNum from @tmp1 group by sip, Fund, isnull(ShortName, '') ), Q2 as ( select Q1.SumAmount, tce.* from @tmp1 tce inner join Q1 on Q1.sip = tce.sip and Q1.Fund = tce.Fund and Q1.ShortName = isnull(tce.ShortName, '') and Q1.MaxTransNum = tce.TransNum ) update cpe set cpe.SettleDate = tce.SettleDate, cpe.TrnType = tce.TrnType, cpe.SecDesc = tce.SecDesc, cpe.Par = tce.Par, cpe.Price = tce.Price, cpe.AInterest = tce.AInterest, cpe.SecType = tce.SecType, cpe.Loc = tce.Loc, cpe.code = tce.code, cpe.ProductType = tce.ProductType, cpe.TransNum = tce.TransNum, cpe.Tdate = tce.TDate, cpe.PKey = tce.PKey from @tmp2 cpe inner join Q2 tce on tce.SIP = cpe.SIP and tce.SumAmount = cpe.Amount and tce.shortName = isnull(cpe.ShortName, '')
Spunny 366 Reputation points

2025-03-28T21:27:31.58+00:00

Hi Viorel,

thank you. your suggestion helped. it worked.

Answer 1

Your query isn't going to work because you are joining on SIP and Amount. Amount in tmp1 is the original amount in your data. Amount in tmp2 is the sum of the grouping by SIP you did on tmp1. Unless there is a single row for each SIP they would never be equal.

An additional problem you have is that multiple rows in tmp1 map to the same row in tmp2 so which SettleDate, TinType, etc are you actually wanting to use? Unless they are the same for all the rows with the same SIP and ShortName then it won't work.

I'm not really sure what your ultimate goal is here but it seems like you want to group the tmp1 data by SIP/ShortName into tmp2. Is that correct? In that case the Amount doesn't matter as part of the join. What you need to decide is where the rest of the values are coming from. Do you want the highest (MAX), lowest (MIN), or what?

I also don't know how much of this you actually need in temp tables. You can use a subquery to do the grouping and aggregate and join it back to the original table to get your results. The second table is only useful if you need to do subsequent processing.

Here's a query that groups by the sip, fund and shortname. Any columns in tmp1 that will have different values for rows with the same group columns should be in the select for the grouping and you need to decide which value you want. The remaining values can come from the original table. The distinct eliminates duplicate rows.

--INSERT INTO @tmp2 (...)
SELECT DISTINCT updated.sip, updated.fund, updated.ShortName, updated.TotalAmount, updated.LatestSettleDate, updated. LatestTDate
     , updated.HighestPar, updated.HighestPrice, updated.LatestTransNum
     , original.TrnType, original.SecDesc, original.AInterest
	 , original.SecType, original.Loc, original.code, original.ProductType, original.PKey
FROM @tmp1 original
	JOIN (SELECT sip, fund, ShortName, SUM(Amount) TotalAmount, MAX(SettleDate) LatestSettleDate, MAX(tdate) LatestTDate, MAX(Par) HighestPar
	           , MAX(Price) HighestPrice, MAX(TransNum) LatestTransNum
		  FROM @tmp1
          GROUP BY sip, Fund, ShortName) updated ON original.sip = updated.sip AND original.fund = updated.fund AND original.ShortName = updated.ShortName

Alternatively you can put all the columns in the grouping select and eliminate the need for the join back to the original table and the distinct. It is a preference to me, possibly with some profiling to check performance.

Share via

SQL Query with group by and update

1 answer

Your answer