CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
PD SQL
PD SQL

Reputation: 59

Selective Aggregation (GROUP BY specific rows in the table)

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

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

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

Jon Tofte-Hansen
Jon Tofte-Hansen

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

Related Questions