Solved

# Calculate ratio to total percentages in SQL Server 2000

Posted on 2008-11-12
2,177 Views
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(%)

0
Question by:drweber

LVL 59

Expert Comment

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
0

LVL 59

Expert Comment

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
``````
0

Author Comment

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

0

LVL 59

Expert Comment

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

Author Comment

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
0

Author Comment

Anyone else out there have an idea on this?
0

LVL 40

Expert Comment

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
0

Author Comment

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)
0

Author Comment

Sorry, forgot to answer, each category is already unique, done in a previous step
0

LVL 40

Expert Comment

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
0

LVL 59

Accepted Solution

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
``````
0

Author Comment

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
0

LVL 59

Expert Comment

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
``````
0

Author Comment

mwvisa - that works! now just need to round the results down to 2 DP - where does that go? I'm used to Oracle rounding
0

LVL 40

Expert Comment

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
0

LVL 40

Expert Comment

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
0

LVL 59

Expert Comment

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
``````
0

Author Comment

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

LVL 40

Expert Comment

Did you check my query?
0

Author Comment

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
0

LVL 40

Expert Comment

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
0

Author Comment

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
0

LVL 40

Assisted Solution

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
0

Author Closing Comment

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

Author Comment

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
``````
0

## Featured Post

### Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that â€¦
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in beâ€¦
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE sâ€¦