Calculating percentages

Question

Calculating percentages

ken.trock

SSCertifiable

Points: 5147
More actions
January 6, 2012 at 4:29 pm

#262579

Hello. Given this query on my DB
SELECT
CASE
WHEN PreFragmentationPercent BETWEEN 0 AND 4 THEN '1. < 5'
WHEN PreFragmentationPercent BETWEEN 5 AND 29 THEN '2. 5 - 29'
ELSE '3. > 30' END AS 'FragLevel',
count(*) AS 'FragCount'
FROM Indexing.tblIndexStatus i1 WHERE IndexActive = 1
GROUP BY CASE
WHEN PreFragmentationPercent BETWEEN 0 AND 4 THEN '1. < 5'
WHEN PreFragmentationPercent BETWEEN 5 AND 29 THEN '2. 5 - 29'
ELSE '3. > 30' END
I get the following results:
1. < 5297
2. 5 - 29192
3. > 3099
Without calculating the total in a seperate query how can I calculate each lines percentage of the total like such:
Pct of total
1. < 529750.51%
2. 5 - 2919232.65%
3. > 309916.84%
Thanks,
Ken

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 1

You know te rules by now, Ken... we need data for these types of things. I'll provide the data this time but, as a reminder, read the first link in my signature line below for how to post such problems. 😉

--===== Conditionally drop the test table to make reruns

-- in SSMS easier. This is NOT a part of the solution.

IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL

DROP TABLE #MyHead

;

--===== Create and populate the test table.

-- This is NOT a part of the solution.

SELECT TOP 1000

PreFragmentationPercent = ABS(CHECKSUM(NEWID()))%101,

IndexActive = ABS(CHECKSUM(NEWID()))%2

INTO #MyHead

FROM sys.all_columns ac1,

sys.all_columns ac2

;

--===== Solve the problem

WITH

ctePreCat AS

(

SELECT FragLevel = CASE

WHEN PreFragmentationPercent BETWEEN 0 AND 4 THEN '1. < 5'

WHEN PreFragmentationPercent BETWEEN 5 AND 29 THEN '2. 5 - 29'

WHEN PreFragmentationPercent > 30 THEN '3. > 30'

ELSE '4. Unknown'

END

FROM #MyHead

WHERE IndexActive = 1

),

ctePreAgg AS

(

SELECT FragLevel,

FragCount = COUNT(*)

FROM ctePreCat

GROUP BY FragLevel

)

SELECT FragLevel,

FragCount,

[Pct of Total] = CAST(CAST(FragCount*100.0/(SUM(Fragcount) OVER (PARTITION BY (SELECT NULL))) AS DECIMAL(5,2)) AS VARCHAR(6)) + '%'

FROM ctePreAgg

ORDER BY FragLevel

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ken.trock SSCertifiable Points: 5147 More actions · Answer 2

Thanks Jeff. Guess the key is knowing how to use the OVER clause for something else besides ROW_NUMBER().

And that's a nifty way to get a lot of test data in there; shades of the tally table.

Ken

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 3

Thanks for the feedback, Ken. Heh... I always wonder if people notice how I sometimes generate test data for these posts. I guess the answer is that at least one person does. 🙂 And, yeah... it does use the same method I use for generating a Tally Table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Revenant SSC-Forever Points: 42467 More actions · Answer 4

It is said that the devil never sleeps.

Just of my curiosity: Jeff, do you ever sleep? 😉