Answered by:
Trying to calculate a relative percentage over a set

-
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.
Question
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
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
- Edited by Kalman TothModerator Tuesday, July 08, 2014 10:30 PM
-
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.
-
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
-
-
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.
-
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
-
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