February 24, 2016 at 8:01 am
hey gents - I have a query that shows me distinct codes from a column called NT_ID as well as their corresponding frequency. I want to add a third column that will allow me to see what percentage of the total is attributed to a particular code.
So for example, I have about 41 distinct codes listed in column A (NT_ID) and the number of times each NT_ID appears in the data set in column B (Frequency) and now I would like to know what the percentage for that frequency is. Take NT_ID = '0000'...it appears 10,000 times and of the total of records in the table, that accounts for 10% of the total, which is 100,000 records.
I'm stuck on the code for how to create the percentage of the total column for each code listed in column A. Here is my code:
select distinct
case
when REVNU_CD is null then '99999'
else REVNU_CD
END AS REVNUCD,
CAST(COUNT(CASE WHEN REVNU_CD IS NULL THEN '99999' ELSE REVNU_CD END) AS INT) AS FREQ
GROUP BY 1
from CCW_VIEW_PRD.MED_PD_CLM_TRNSMSSN;
February 24, 2016 at 8:21 am
Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 24, 2016 at 8:24 am
tindog (2/24/2016)
Could you post some sample data please? Will be easier to help.Regards
Not A Gent
Hello tindog - I can't seem to upload any data so I could supply some examples
NT_ID
A1005
A1005
A2005
A2300
A2301
A2301
A2302
So with the above sample, I created a second column called Frequency which would then show distinct values (so only 5 records) where in this column I would have the corresponding number of times that NT_ID appeared. So in the case of A1005, it would show 1x with a count of 2. And then with the third column for percentage, since in this sample I have a total of 7 records where A1005 appears 2 out of the 7 times, the percentage would be 28.57%.
February 24, 2016 at 8:35 am
tindog (2/24/2016)
Are you after something like this:
SELECT NT_ID,
100 * Frequency / (SUM(Frequency) OVER ()) PercentageOfTotal
FROM ........
ETA: prefix with 100 *
Yes!!!! This is awesome...thank you very much!
One more thing...I'm trying to cast the PercentageOfTotal as a percentage with 2 digits (so 70.25). Is that possible?
February 24, 2016 at 8:38 am
jakebeliveau (2/24/2016)
tindog (2/24/2016)
Are you after something like this:
SELECT NT_ID,
100 * Frequency / (SUM(Frequency) OVER ()) PercentageOfTotal
FROM ........
ETA: prefix with 100 *
Yes!!!! This is awesome...thank you very much!
One more thing...I'm trying to cast the PercentageOfTotal as a percentage with 2 digits (so 70.25). Is that possible?
Sure:
SELECT
CAST((100 * Frequency / (SUM(Frequency) OVER ())) AS DECIMAL(5,2)) PercentageOfTotal
February 24, 2016 at 8:39 am
jakebeliveau (2/24/2016)
tindog (2/24/2016)
Are you after something like this:
SELECT NT_ID,
100 * Frequency / (SUM(Frequency) OVER ()) PercentageOfTotal
FROM ........
ETA: prefix with 100 *
Yes!!!! This is awesome...thank you very much!
One more thing...I'm trying to cast the PercentageOfTotal as a percentage with 2 digits (so 70.25). Is that possible?
Sure it's possible. You first have to make sure you are doing division with decimals instead of ints. Then you round the result to 2 decimal places.
Something like this.
SELECT NT_ID,
ROUND(100.0 * Frequency / (SUM(Frequency) OVER ()), 2) PercentageOfTotal
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 24, 2016 at 8:52 am
Sean Lange (2/24/2016)
jakebeliveau (2/24/2016)
tindog (2/24/2016)
Are you after something like this:
SELECT NT_ID,
100 * Frequency / (SUM(Frequency) OVER ()) PercentageOfTotal
FROM ........
ETA: prefix with 100 *
Yes!!!! This is awesome...thank you very much!
One more thing...I'm trying to cast the PercentageOfTotal as a percentage with 2 digits (so 70.25). Is that possible?
Sure it's possible. You first have to make sure you are doing division with decimals instead of ints. Then you round the result to 2 decimal places.
Something like this.
SELECT NT_ID,
ROUND(100.0 * Frequency / (SUM(Frequency) OVER ()), 2) PercentageOfTotal
Sean, thanks for clarifying that point. This solution is perfect.
February 24, 2016 at 8:55 am
Sean Lange (2/24/2016)
jakebeliveau (2/24/2016)
tindog (2/24/2016)
Are you after something like this:
SELECT NT_ID,
100 * Frequency / (SUM(Frequency) OVER ()) PercentageOfTotal
FROM ........
ETA: prefix with 100 *
Yes!!!! This is awesome...thank you very much!
One more thing...I'm trying to cast the PercentageOfTotal as a percentage with 2 digits (so 70.25). Is that possible?
Sure it's possible. You first have to make sure you are doing division with decimals instead of ints. Then you round the result to 2 decimal places.
Something like this.
SELECT NT_ID,
ROUND(100.0 * Frequency / (SUM(Frequency) OVER ()), 2) PercentageOfTotal
Ignore my last post - this is correct.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply