Reputation: 59
I have table where I need to group the amount for selective (specific rows).
Group by the rows from funds RLWP, RLNP/UL and RL Estate into a single row by Asset Name.
Rename the Fund Number Field to RP for > RLWP, RLNP/UL and RL Estate but I don’t want to group or sum where fund name not in (> RLWP, RLNP/UL and RL Estate)
Asset Name |Entity Code |Total SII Amount |Fund Number
------------------------------------------------------------
GSW 4.375% |1020OB |1317697.53 |RLWP
GSW 4.375% |1020OB |1191368.74 |RLNP/UL
GSW 4.375% |1020OB |289004.18 |RL Estate
GSW 4.375% |1020OB |232360.70 |UFOIB
GSW 4.375% |1020OB |10102.50 |ABG
UKT 0.375% |1020OB |5555.20 |ABG
UKT 0.375% |1020OB |702743.3518 |RLWP
UKT 0.375% |1020OB |546232.3383 |RL Estate
I have 2 or 3 ways we can do it, But could you suggest the most efficient way as this is just one part of several other calculation on the same table in a stored procedure.
I think of one way is
Select
[Asset Name],
[Entity Code],
sum([Total SII Amount])
where
fundnumber in (RLWP, [RLNP/UL], RL Estate)
group by
[Asset Name], [Entity Code],
union
Select
[Asset Name],
[Entity Code],
where
fundnumber not in (RLWP, [RLNP/UL], RL Estate)
And later update the column name to RP for (RLWP, [RLNP/UL] ,RL Estate).
Other way I think of using CTE or Cross Apply but Couldn’t really make up the query.
Please suggest.
Upvotes: 2
Views: 697
Reputation: 67341
You asked me for an example, here you are: Just paste into an empty query window and execute, adapt to your needs...
DECLARE @Asset TABLE([Asset Name] VARCHAR(100),[Entity Code] VARCHAR(100), [Total SII Amount] DECIMAL(12,4),[Fund Number] VARCHAR(100) );
INSERT INTO @Asset VALUES
('GSW 4.375%','1020OB',1317697.53,'RLWP')
,('GSW 4.375%','1020OB',1191368.74,'RLNP/UL')
,('GSW 4.375%','1020OB',289004.18,'RL Estate')
,('GSW 4.375%','1020OB',232360.70,'UFOIB')
,('GSW 4.375%','1020OB',10102.50,'ABG')
,('UKT 0.375%','1020OB',5555.20,'ABG')
,('UKT 0.375%','1020OB',702743.3518,'RLWP')
,('UKT 0.375%','1020OB',546232.3383,'RL Estate');
--This comes back with all rows and a grouped sum at the end
WITH AssetsWithGroupedFundNumber AS
(
SELECT *
,CASE [Fund Number]
WHEN 'RLWP' THEN 'RP'
WHEN 'RLNP/UL' THEN 'RP'
WHEN 'RL Estate' THEN 'RP'
ELSE [Fund Number]
END AS FundNumberGrouped
FROM @Asset
)
SELECT *
,SUM(a.[Total SII Amount]) OVER(PARTITION BY a.FundNumberGrouped) AS GroupSum
FROM AssetsWithGroupedFundNumber AS a;
--This comes back with a "normal" grouped sum
WITH AssetsWithGroupedFundNumber AS
(
SELECT *
,CASE [Fund Number]
WHEN 'RLWP' THEN 'RP'
WHEN 'RLNP/UL' THEN 'RP'
WHEN 'RL Estate' THEN 'RP'
ELSE [Fund Number]
END AS FundNumberGrouped
FROM @Asset
)
SELECT a.FundNumberGrouped
,SUM(a.[Total SII Amount]) AS GroupSum
FROM AssetsWithGroupedFundNumber AS a
GROUP BY a.FundNumberGrouped
Upvotes: 1
Reputation: 794
If I understand you correctly, this could be an answer:
SELECT [Asset Name],
[Entity Code],
SUM ([Total SII Amount]) [Total SII Amount],
case [Fund Number]
when 'RLWP' then 'RP'
when 'RLNP/UL' then 'RP'
when 'RL Estate' then 'RP'
else [Fund Number]
end
[Fund Number]
FROM tab
GROUP BY [Asset Name],
[Entity Code],
case [Fund Number]
when 'RLWP' then 'RP'
when 'RLNP/UL' then 'RP'
when 'RL Estate' then 'RP'
else [Fund Number]
end
Upvotes: 3