# Calculate ratio to total percentages in SQL Server 2000

I've looked through several question/responses on this topic, and tried some of the solutions and have not been able to get this to work...

In Oracle, I would use ratio to report to calculate the percentage of the total that each category covers, to come up with a total pct of 100.    Unfortunately I'm working in SQL Server 2000 and I've not found an easy way to do this.

I found some solutions in SQL cookbook but they involve selecting a specfic category, and I want this additional calculation/column for all categories and that is throwing me off.

I have the following table where 1st column is a category,  the 2nd column is a count
I want to find and write to a 3rd column the value that comes from Cat A (61) / Total (37060) =  0.16%
and then I need a total line with the total count, and total %

I'm not opposed to adding a total to the original table and referencing that, although I think there is probably a cleaner way to do it.

Cat A                61
Cat B              298
Cat C                74
Cat D            1554
Cat E               191
Cat F           33057
Cat G                   1825

The result I want:
Cat A      61      0.16
Cat  B      298      0.80
Cat C      74      0.20
Cat D      1554      4.20
Cat E      191      0.52
Cat F      33057      89.29
Cat G      1825      4.93
Total             37020           100(%)

###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chief Technology OfficerCommented:
SELECT a.Category
, Count(a.*) AS CategoryCount
, (Count(a.*) * 100.0)/b.TotalCount AS CategoryRatio
FROM TableName a
OUTER APPLY (SELECT Count(*) As TotalCount FROM TableName) b
GROUP BY a.Category
UNION ALL
SELECT 'Total', COUNT(*), 100.0
FROM TableName
Chief Technology OfficerCommented:
This gets the total count and applies to all the rows grouped by category.  Didn't put in any checks for 0/null as you will not get any rows in a scenario where count from same table is null/0 in the first place so it would be a mute point.

I was trying the outer apply for performance reasons as I think that performs a little quicker, but basically this is my suggestion.

``````SELECT a.Category
, Count(a.*) AS CategoryCount
, (Count(a.*) * 100.0)/(SELECT Count(*) FROM TableName) AS CategoryRatio
FROM TableName a
GROUP BY a.Category
UNION ALL
SELECT 'Total', COUNT(*), 100.0
FROM TableName

-- You can try the WITH Rollup command as well:
SELECT a.Category
, Count(a.*) AS CategoryCount
, (Count(a.*) * 100.0)/(SELECT Count(*) FROM TableName) AS CategoryRatio
FROM TableName a
GROUP BY a.Category WITH ROLLUP
``````
Author Commented:

1)  I'm getting errors when I swap in my table name #ytd8

SELECT a.Category,
Count(a.*) AS CategoryCount,
(Count(a.*) * 100.0)/b.TotalCount AS CategoryRatio
FROM #ytd8 a
OUTER APPLY (SELECT Count(*) As TotalCount FROM #ytd8) b
GROUP BY a.Category
UNION ALL
SELECT 'Total', COUNT(*), 100.0
FROM #ytd8

Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '*'.
Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'b'.

2) the union statement 'Total' is only giving me a count of 8 instead of count of 37020

Total      8      100.0

Chief Technology OfficerCommented:
What are the contents of #ytd8?  A sample of the data would be good enough unless the record count is 8 then please post all 8 records.
Author Commented:
I posted exactly what is in #YTD8, you've got all 8 records (the first "table" in the intial post)

It's a temp table, added up from several other tables
Author Commented:
Anyone else out there have an idea on this?
Data EngineerCommented:
From your sample data set, I assumed that you have a column as Count1 which holds the count for each category. Is category is unique in your table or do you have more than one row for same category?
check the below query.

SELECT a.Category
,a.Count1
,a.Count1/SUM(a.Count1) AS Ratio
FROM #ytd8  a
GROUP BY a.Category, a.Count1
UNION ALL
SELECT 'Total' AS Category
,SUM(Count1) AS Count1
,100 AS Ratio
FROM #ytd8
Author Commented:
Sharath, using what you suggested

SELECT a.Category
,a.count
,a.count/SUM(a.count) AS Ratio
FROM #ytd8  a
GROUP BY a.Category, a.count

returns:

cat1      61      1
cat 2      74      1
cat3      191      1
cat4      298      1
cat5            1554      1
cat6      1825      1
cat7      33057      1

Not really what I'm looking for, I need the percentages listed in the original posting

The values in the Count column are already totaled from another table, i just need to divide the count for each category by the total count (37020)
Author Commented:
Sorry, forgot to answer, each category is already unique, done in a previous step
Data EngineerCommented:
Check this:
SELECT a.Category
,a.Count1
,CAST(a.Count1 AS MONEY)/CAST(SUM(a.Count1) AS MONEY) AS Ratio
FROM #ytd8  a
GROUP BY a.Category, a.Count1
UNION ALL
SELECT 'Total' AS Category
,SUM(Count1) AS Count1
,100 AS Ratio
FROM #ytd8
Chief Technology OfficerCommented:
That is my fault, I was looking at that as a query result and your table had details.  This is more simple then.

You will have to change column names, but here is what I did to test.
``````-- my test data --
CREATE TABLE #ytd8 (Category VARCHAR(50), CategoryCount INT)

INSERT INTO #ytd8
SELECT 'Cat A', 61
UNION ALL SELECT 'Cat B', 298
UNION ALL SELECT 'Cat C', 74
UNION ALL SELECT 'Cat D',            1554
UNION ALL SELECT 'Cat E',               191
UNION ALL SELECT 'Cat F',           33057
UNION ALL SELECT 'Cat G',                   1825
-- my test data --

SELECT a.Category
, CategoryCount
, (CategoryCount * 100.0)/(SELECT SUM(CategoryCount) FROM #ytd8) AS CategoryRatio
FROM #ytd8 a
GROUP BY a.Category, a.CategoryCount
UNION ALL
SELECT 'Total', SUM(CategoryCount), 100.0
FROM #ytd8
``````

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Sharath, when I do that I still get  back all ratios as 1.00
I need to know the % that 61 makes up in the overall total (which is .16%)

ECT      61      1.00
Sub Acute      74      1.00
Mileage      191      1.00
Injections      298      1.00
Hospital Inpat1554      1.00
Inpatient      1825      1.00
Outpatient      33057      1.00
Chief Technology OfficerCommented:
ANd here it is with my first example using OUTER APPLY.  Also for the above, the GROUP BY is leftover code.  It still works but is unnecessary since we are not using any aggregates in top portion.
``````SELECT a.Category
, a.CategoryCount
, (a.CategoryCount * 100.0)/b.TotalCount AS CategoryRatio
FROM #ytd8 a
OUTER APPLY (SELECT SUM(CategoryCount) As TotalCount FROM #ytd8) b
UNION ALL
SELECT 'Total', SUM(CategoryCount), 100.0
FROM #ytd8
``````
Author Commented:
mwvisa - that works! now just need to round the results down to 2 DP - where does that go? I'm used to Oracle rounding
Data EngineerCommented:
Sorry, in my previous query, I am dividing 61 with 61 only. mwvisa1's query should work for you. I changed my previous query. check this one:
SELECT a.Category
,a.Count1
,(CAST(a.Count1 AS MONEY)/CAST((SELECT SUM(Count1) FROM #ytd8) AS MONEY) )AS Ratio
FROM #ytd8  a
GROUP BY a.Category, a.Count1
UNION ALL
SELECT 'Total' AS Category
,SUM(Count1) AS Count1
,100 AS Ratio
FROM #ytd8
Data EngineerCommented:
SELECT a.Category
,a.Count1
,ROUND(CAST(a.Count1 AS MONEY)/CAST((SELECT SUM(Count1) FROM #ytd8) AS MONEY),2) AS Ratio
FROM #ytd8  a
GROUP BY a.Category, a.Count1
UNION ALL
SELECT 'Total' AS Category
,SUM(Count1) AS Count1
,100.00 AS Ratio
FROM #ytd8
Chief Technology OfficerCommented:
Here it is...
``````SELECT a.Category
, a.CategoryCount
, ROUND((a.CategoryCount * 100.0)/b.TotalCount, 2) AS CategoryRatio
FROM #ytd8 a
OUTER APPLY (SELECT SUM(CategoryCount) As TotalCount FROM #ytd8) b
UNION ALL
SELECT 'Total', SUM(CategoryCount), 100.00
FROM #ytd8
``````
Author Commented:
mwvisa - I'm getting errors when trying to use the code with the outer apply...
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'OUTER'.
Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'b'.

Is there  a way to do the rounding with the other code?
Data EngineerCommented:
Did you check my query?
Author Commented:
Sharath, This is close but I need %  - everything in the ratio column multiplied by 100

ECT      61      0.0016
Hospital Inpatient      1554      0.0419
Injections      298      0.008
Inpatient      1825      0.0492
Mileage      191      0.0051
Outpatient      33057      0.8919
Sub Acute      74      0.0019
Total      37060      100.00
Data EngineerCommented:
Simply multiply with 100.
SELECT a.Category
,a.Count1
,ROUND(CAST(a.Count1 AS MONEY)*100/CAST((SELECT SUM(Count1) FROM #ytd8) AS MONEY),2) AS Ratio
FROM #ytd8  a
GROUP BY a.Category, a.Count1
UNION ALL
SELECT 'Total' AS Category
,SUM(Count1) AS Count1
,100.00 AS Ratio
FROM #ytd8
Author Commented:
It's not rounding to 2 DP, even though you have the ROUND function in the command to calculate the percentage

ECT      61      0.1600
Hospital       1554      4.1900
Injections      298      0.8000
Inpatient      1825      4.9200
Mileage      191      0.5200
Outpatient      33057      89.2000
Sub Acute      74      0.2000
Total      37060      100.0000
Data EngineerCommented:
check this:
SELECT a.Category
,a.Count1
,ROUND((CAST(a.Count1 AS MONEY) * 10000 / CAST((SELECT SUM(Count1) FROM #ytd8) AS MONEY))/100,2) AS Ratio
FROM #ytd8  a
GROUP BY a.Category, a.Count1
UNION ALL
SELECT 'Total' AS Category
,SUM(Count1) AS Count1
,100.00 AS Ratio
FROM #ytd8
Author Commented:
You both helped me get to where I wanted to go with this, although not directly. I ended up creating temp tables with the percentages for each group (I had multiple sets of these to do), then joining them together and rounding as part of the join/create combined table process.
Author Commented:
You both helped me get to where I wanted to go with this, although not directly. I ended up creating temp tables with the percentages for each group (I had multiple sets of these to do), then joining them together and rounding as part of the join/create combined table process.
ytdconsumers is what we referred to as "Count 1" throughout the process
consider ytdpaidamt as a "Count 2"

here is my table (without the totals), they were doing the totals in another step further down that I hadn't gotten to yet:
ECT      61      0.16      28146.64       0.09
Hospital In      1554      4.19      4214566.44       13.39
Injections      298      0.80      22326.24       0.07
Inpatient      1825      4.92      308814.30          0.98
Mileage      191      0.52      20253.47       0.06
Outpatient      33057      89.20      26819779.94       85.21
Sub Acute      74      0.20      62696.25       0.20
``````
-- create table with ytd consumer pct

drop table #ytd8
SELECT Category,
ytdconsumers,
(ytdconsumers * 100.0)/(SELECT SUM(ytdconsumers) FROM #ytd7)AS ytd_pct,
consumers,
consumer_pct
into #ytd8
FROM #ytd7
order by category

-- create table with ytd amt paid pct
drop table #ytd9
SELECT Category,
ytdpaidamt,
(ytdpaidamt* 100.0)/(SELECT SUM(ytdpaidamt) FROM #ytd7)AS ytd_pct,
paidamt,
paidamt_pct
into #ytd9
FROM #ytd7
order by category

-- create consolidated table of categories, ytd, rounded pct for consumers and amt paid

drop table #ytd10
select y8.category, y8.ytdconsumers,
ROUND(CAST(y8.ytd_pct as MONEY),2) as ytd_cons_pct,
y8.consumers, y8.consumer_pct,y9.ytdpaidamt,
ROUND(CAST(y9.ytd_pct as MONEY),2) as _ytd_paid_pct,
y9.paidamt, y9.paidamt_pct

into #ytd10
from #ytd8 y8
inner join  #ytd9 y9 on y8.category=y9.category
order by y8.category
``````
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.