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.

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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

Answer 7

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.