Two levels of grouping on one set of data. Is it possible

I’m running a query that breaks up percentages by country.. something like this:

 select country_of_risk_name, (sum(isnull(fund_weight,0)*100)) as 'FUND WEIGHT' from       OFI_Country_Details
WHERE FIXED_COMP_FUND_CODE = 'X'
GROUP BY country_of_risk_name

This returns me the right output. This can range anywhere from 1 Country to 100 countries. How can I write my logic that it shows me the top 5 highest percentages and then groups all those outside the top 5 into an ‘Other’ category? Example output:

  • Query to return data where Id Column is unique but display number is the same?
  • Best method for determining if a row exists
  • Migrate SQL Server DateTime column to DateTimeOffset
  • Cancelling jQuery POST to ASP.Net WebMethod
  • How do I perform a GROUP BY on an aliased column in MS-SQL Server?
  • Setting a variable in an exec() that was declared outside the exec() in SQL server
    1. USA – 50%
    2. Canada – 10%
    3. France – 4%
    4. Spain – 2%
    5. Italy – 1.7%
    6. Other – 25%

  • How to show Price Variance in Result Set (Purchase Order Pricing)
  • Add total row to bottom of SQL Query
  • SQL XML Multiple Elements Per Row
  • SQL sum across rows with cross-column “matching”?
  • Multiple aggregations with different where clauses
  • (SQL) Grouping by End of week
  • 4 Solutions collect form web for “Two levels of grouping on one set of data. Is it possible”

    SELECT rn, CASE WHEN rn <= 5 THEN x.country_of_risk_name
                    ELSE 'Other' END AS country_of_risk_name, 
               SUM(x.[FUND WEIGHT]) AS SumPerc
    FROM(      
         SELECT country_of_risk_name,
                CASE WHEN ROW_NUMBER() OVER(ORDER BY SUM(ISNULL(fund_weight,0)*100) DESC) <= 5
                     THEN ROW_NUMBER() OVER(ORDER BY SUM(ISNULL(fund_weight,0)*100) DESC)
                     ELSE 6 END AS rn,
                SUM(ISNULL(fund_weight,0)*100) AS [FUND WEIGHT]
         FROM country_of_risk_name
         WHERE FIXED_COMP_FUND_CODE = 'X'
         GROUP BY country_of_risk_name
         ) x
    GROUP BY rn, CASE WHEN rn <= 5 THEN x.country_of_risk_name
                      ELSE 'Other' END 
    ORDER BY x.rn
    

    See demo on SQLFiddle

    Here’s an ugly way to do this using just SQL:

    select country, sum(perc)
    from (
      select 
        case when rn <= 5 then country else 'Other' end 'Country',
        case when rn <= 5 then rn else 6 end rn,    
        perc
      from (
        select *, row_number() over (order by perc desc) rn
        from yourresults
        ) t
    ) t
    group by country, rn
    order by rn
    
    • SQL Fiddle Demo

    I’ve used yourresults as the results of your above query — throw that in a Common Table Expression and you should be good to go:

    with yourresults as (
        select country_of_risk_name, (sum(isnull(fund_weight,0)*100)) as 'FUND WEIGHT' 
        from OFI_Country_Details
        where FIXED_COMP_FUND_CODE = 'X'
        group by country_of_risk_name
    )
    ...
    

    Here is the query using CTE: Also a working solution example

    ;with TotalPercent(Country_Of_Risk_Name, Fund_Weight)
    as(
        select Country_Of_Risk_Name, sum(isnull(Fund_Weight, 0) * 100) Fund_Weight
        from OFI_Country_Details
        where
           FIXED_COMP_FUND_CODE = 'X'
        group by Country_Of_Risk_Name
      ),
      PercentWithRank(Country_Of_Risk_Name, Fund_Weight, RowNumber)
      as (
          select Country_Of_Risk_Name, Fund_Weight, row_number() over (order by Fund_Weight desc) RowNumber
          from TotalPercent
      )
      select Country_Of_Risk_Name, Fund_Weight
      from PercentWithRank
      where
          RowNumber <= 5
      union all
      select 'Other', sum(Fund_Weight) Fund_Weight
      from PercentWithRank
      where
          RowNumber > 5
    

    Try this.

        declare  @country_of_risk_name as table (country varchar(100),
                                  FUND decimal(10,2));
    
        insert into @country_of_risk_name values
          ('USA', 50),
          ('Canada', 10),
          ('France', 4),
          ('Spain', 2),
          ('Italy', 1.7),
          ('Other1', 1.5),
          ('Other2', 1.5),
          ('Other3', 1.5),
          ('Other4', 1.5),
          ('Other5', 1.5),
          ('Other6', 1.5);
    
    
    
        DECLARE @TBL AS TABLE(country_of_risk_name VARCHAR(100), FUND DECIMAL(18,2))
    
        INSERT INTO @TBL
        SELECT TOP 5 country,SUM(FUND) FROM @country_of_risk_name
          group by country
          ORDER BY SUM(FUND) desc
          select * from @TBL
          UNION ALL 
          select 'other', SUM(tbl1.fund) 
          from @country_of_risk_name tbl1 
          left join @TBL tbl2 on tbl1.country =tbl2.country_of_risk_name 
          where 
              tbl2.country_of_risk_name  is null 
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.