none
Cummulative percentage in SQL Server 2012 RRS feed

  • Question

  • Hello

    I would like to know if the new analytical functions in SQL Server 2012 allow us to create easily cummulative percentage queries (as in creating a Pareto table).

    Thanks a lot.

    Monday, August 6, 2012 9:51 PM

Answers

  • Hi As far as I know that there is no such analytic function added in SQL server 2012 for cumulative percentage purpose as you need for Pareto chart.

    The new function CUME_Dist is used to calculate the cumulative rank percent, which seems not suite for your needs.

    In SSRS, you can easily generate Pareto chart without precalculating the cumulative percentage as the SSRS has built-in the chart type for you. (If I remembered correctly you just use the column chart and change the chart type in property.)

    However, in SQL server 2012 as it introduced the Frame clause for aggregation functions, it is very easy to calculate what you need. I setup a scenario as below:

    Declare @MyTable Table (Category varchar(20), MyValue int);
    
    Insert into @MyTable
    Select 'A', 20 Union All
    Select 'B', 10 Union All
    Select 'C', 120 Union All
    Select 'D', 90 Union All
    Select 'E', 20 Union All
    Select 'F', 4 Union All
    Select 'G', 9 Union All
    Select 'H', 190 Union All
    Select 'I', 10 Union All
    Select 'J', 30 Union All
    Select 'K', 3
    
    Select *
    		, Cast(1.0 * Sum(MyValue) Over(	Order By MyValue Desc
    										Rows Unbounded Preceding
    										) / Sum(MyValue) Over() As decimal(5,2)) As CumulativePct
    From @MyTable


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!


    Monday, August 6, 2012 11:55 PM
  • Hi,

    You can use the OVER for this. It works with SQL Server 2008 as well.

    USE [tempdb];
    GO
    
    IF OBJECT_ID('tempdb..#test') IS NOT NULL
    	DROP TABLE #test;
    
    CREATE TABLE #test
    (
    	id int identity
    );
    GO
    
    INSERT INTO #test DEFAULT VALUES 
    GO 10
    
    SELECT
    	id,
    	100* cast(id as decimal) / SUM(id) over()
    FROM
    	#test

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    • Marked as answer by EMoscosoCam Tuesday, August 7, 2012 2:06 AM
    Monday, August 6, 2012 10:45 PM

All replies

  • Hi,

    You can use the OVER for this. It works with SQL Server 2008 as well.

    USE [tempdb];
    GO
    
    IF OBJECT_ID('tempdb..#test') IS NOT NULL
    	DROP TABLE #test;
    
    CREATE TABLE #test
    (
    	id int identity
    );
    GO
    
    INSERT INTO #test DEFAULT VALUES 
    GO 10
    
    SELECT
    	id,
    	100* cast(id as decimal) / SUM(id) over()
    FROM
    	#test

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    • Marked as answer by EMoscosoCam Tuesday, August 7, 2012 2:06 AM
    Monday, August 6, 2012 10:45 PM
  • Hi As far as I know that there is no such analytic function added in SQL server 2012 for cumulative percentage purpose as you need for Pareto chart.

    The new function CUME_Dist is used to calculate the cumulative rank percent, which seems not suite for your needs.

    In SSRS, you can easily generate Pareto chart without precalculating the cumulative percentage as the SSRS has built-in the chart type for you. (If I remembered correctly you just use the column chart and change the chart type in property.)

    However, in SQL server 2012 as it introduced the Frame clause for aggregation functions, it is very easy to calculate what you need. I setup a scenario as below:

    Declare @MyTable Table (Category varchar(20), MyValue int);
    
    Insert into @MyTable
    Select 'A', 20 Union All
    Select 'B', 10 Union All
    Select 'C', 120 Union All
    Select 'D', 90 Union All
    Select 'E', 20 Union All
    Select 'F', 4 Union All
    Select 'G', 9 Union All
    Select 'H', 190 Union All
    Select 'I', 10 Union All
    Select 'J', 30 Union All
    Select 'K', 3
    
    Select *
    		, Cast(1.0 * Sum(MyValue) Over(	Order By MyValue Desc
    										Rows Unbounded Preceding
    										) / Sum(MyValue) Over() As decimal(5,2)) As CumulativePct
    From @MyTable


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!


    Monday, August 6, 2012 11:55 PM