none
Trying to calculate a relative percentage over a set

    Question

  • Here''s the SQL that I came up with (it doesn't work)

    ;with cte as (select COMPANY as COMPANY, [INDEX MARKET CAP] as IndexCap, Count ([INDEX MARKET CAP]) as CountOfRates
    from [Test_DB].[dbo].[S&P_Emerging_SmallCap_(US_Dollar)]
    group by COMPANY, [INDEX MARKET CAP])
    select *, CAST(CountOfRates*100.0 / SUM(CountOfRates) OVER()  as Decimal(14,14)) as [%]
    FROM cte

    The problem is that the % field never changes!! I want to take each record of [Index Market Cap] and divide by the aggregate of the [Index Market Cap].  I'm sure this can be done with a CTE, but I'm far from a CTE expert, so I'm not sure how to pull this off.

    Please offer guidance


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, July 08, 2014 10:02 PM

Answers

  • Hi!

    Would the following query give you what you want?

    select *, [INDEX MARKET CAP] * 100/SUM([INDEX MARKET CAP]) OVER() as [%]
    FROM [S&P_Emerging_SmallCap_(US_Dollar)] 


    Alexander Karavaev, PMP, MCITP, MCTS
    itconsult.azurewebsites.net

    • Marked as answer by ryguy72 Wednesday, July 09, 2014 1:34 PM
    Wednesday, July 09, 2014 12:33 AM

All replies

  • Here is an AdventureWorks simulation. Let us know what you expect.

    ;WITH CTE AS (SELECT Color, ListPrice, Count(ListPrice) AS CountOfPrices
    FROM Production.Product WHERE Color is not null GROUP BY Color, ListPrice)
    
    SELECT *, CAST (CountOfPrices * 100.0 / SUM (CountOfPrices) 
                OVER() AS DECIMAL(14, 8) ) AS [%]
    FROM CTE ORDER BY Color, ListPrice
    /*
    Color	ListPrice	CountOfPrices	%
    Black	0.00	4	1.56250000
    Black	24.49	3	1.17187500
    Black	34.99	1	0.39062500
    Black	37.99	3	1.17187500
    Black	59.99	4	1.56250000
    Black	60.745	1	0.39062500
    Black	69.99	3	1.17187500
    Black	74.99	3	1.17187500
    Black	85.565	1	0.39062500
    Black	87.745	1	0.39062500
    Black	112.565	1	0.39062500
    Black	175.49	1	0.39062500
    Black	209.025	1	0.39062500
    Black	218.01	1	0.39062500
    Black	236.025	1	0.39062500
    Black	245.01	1	0.39062500
    ....
    */

    Calculate percent on base: http://www.sqlusa.com/bestpractices/percentonbase/


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012









    Tuesday, July 08, 2014 10:29 PM
    Moderator
  • I saw that one before I posted.  ;)

    So, I made a couple changes to get this.

    ;WITH CTE AS (SELECT COMPANY, [INDEX MARKET CAP], Count([INDEX MARKET CAP]) AS CountOfIndex
    FROM [S&P_Emerging_SmallCap_(US_Dollar)] GROUP BY COMPANY, [INDEX MARKET CAP])
    
    SELECT *, CAST (CountOfIndex * 100.0 / SUM (CountOfIndex) 
                OVER() AS DECIMAL(14, 8) ) AS [%]
    FROM CTE ORDER BY COMPANY, [INDEX MARKET CAP]

    The problem is still the same, it doesn't calculate a relative percentage.

    I must be missing a simple little things here, but I don't know what.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, July 08, 2014 10:43 PM
  • Hi!

    Would the following query give you what you want?

    select *, [INDEX MARKET CAP] * 100/SUM([INDEX MARKET CAP]) OVER() as [%]
    FROM [S&P_Emerging_SmallCap_(US_Dollar)] 


    Alexander Karavaev, PMP, MCITP, MCTS
    itconsult.azurewebsites.net

    • Marked as answer by ryguy72 Wednesday, July 09, 2014 1:34 PM
    Wednesday, July 09, 2014 12:33 AM
  • Can you show some sample data and the result you expect out of it?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, July 09, 2014 1:41 AM
  • Thanks Alexander!  This is perfect; exactly what I was looking for!!  I really thought it required a CTE, which I'm not good at.

    Any idea why the CTE query produced all .057372?  That makes no sense to me.

    Thanks again!!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, July 09, 2014 1:16 PM
  • I think it is because of the Count which is missing in Alexander's  query

    Satheesh
    My Blog | How to ask questions in technical forum


    Wednesday, July 09, 2014 1:32 PM
  • Yeap, yeap, it was the Count.  I tweaked it a bit, just for practice.  This works fine.

    ;WITH CTE 
    AS 
    (SELECT COMPANY, [INDEX MARKET CAP]
    FROM [S&P_Emerging_SmallCap_(US_Dollar)] GROUP BY COMPANY, [INDEX MARKET CAP])
    SELECT *, CAST ([INDEX MARKET CAP] * 100.0 / SUM ([INDEX MARKET CAP]) 
                OVER() AS DECIMAL(14, 14) ) AS [%]
    FROM CTE ORDER BY COMPANY, [INDEX MARKET CAP]


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.


    • Edited by ryguy72 Wednesday, July 09, 2014 1:41 PM
    Wednesday, July 09, 2014 1:41 PM