asked on

Quickly Generate Percentages With Frequency in SQL

Hello all,

I often calculate frequencies of values in SQL as follows:

SELECT MyField, COUNT(*) AS [Frequency]
FROM MyTable
GROUP BY MyFIeld

This is a simple quick thing to do and I can type it out in a couple seconds when I'm working through a file trying to get a sense of what's in there.

What I would really like to do is to add the Percentage to my results (just as quickly).

Is there like a secret trick to be able to do that?

TIA!

slightwv (䄆 Netminder)

What database?

This should work in the databases that allow the Window Functions:

SELECT MyField,
	COUNT(*) AS [Frequency],
	sum(COUNT(*) over()) AS [TotalFrequency],
	COUNT(*) / sum(COUNT(*) over()) AS [Pct]
FROM MyTable
GROUP BY MyFIeld

Open in new window

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ttist25

ASKER

Thanks guys.

Ryan, your solution worked for me. As I progress I'm finding CTE's are my good friend. :) Thanks!

slightwv - sorry about that. I usually include my version but I guess I forgot - I'm using MSSQL 2012.

Your solution looks like something I could commit to memory and type out quickly but I get an error "Windowed functions cannot be used in the context of another windowed function or aggregate". Which I assume is why Ryan chose the CTE mode.

Too bad because I really think that would be something I could pop out from memory.

Do you guys know why "PERCENT" is a reserved word? Could it be used to simplify this?

Anyway - thanks again!