# 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.

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

• 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 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
• 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 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?

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

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 Wednesday, July 09, 2014 1:41 PM
Wednesday, July 09, 2014 1:41 PM