?
Solved

Calculate ratio to total percentages in SQL Server 2000

Posted on 2008-11-12
25
Medium Priority
?
2,613 Views
Last Modified: 2012-06-21
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(%)

Thanks in advance!
0
Comment
Question by:drweber
  • 12
  • 7
  • 6
25 Comments
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22941848
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 61

Expert Comment

by:Kevin Cross
ID: 22941893
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

Open in new window

0
 

Author Comment

by:drweber
ID: 22941965
Thanks for the quick reply

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22942183
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

by:drweber
ID: 22942209
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

by:drweber
ID: 22942604
Anyone else out there have an idea on this?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 22943087
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

by:drweber
ID: 22943259
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

by:drweber
ID: 22943272
Sorry, forgot to answer, each category is already unique, done in a previous step
0
 
LVL 41

Expert Comment

by:Sharath
ID: 22943349
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 61

Accepted Solution

by:
Kevin Cross earned 1000 total points
ID: 22943367
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

Open in new window

0
 

Author Comment

by:drweber
ID: 22943370
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 61

Expert Comment

by:Kevin Cross
ID: 22943400
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

Open in new window

0
 

Author Comment

by:drweber
ID: 22943504
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 41

Expert Comment

by:Sharath
ID: 22943508
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 41

Expert Comment

by:Sharath
ID: 22943532
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 61

Expert Comment

by:Kevin Cross
ID: 22943576
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

Open in new window

0
 

Author Comment

by:drweber
ID: 22943881
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 41

Expert Comment

by:Sharath
ID: 22943939
Did you check my query?
0
 

Author Comment

by:drweber
ID: 22944037
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 41

Expert Comment

by:Sharath
ID: 22944071
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

by:drweber
ID: 22944152
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 41

Assisted Solution

by:Sharath
Sharath earned 1000 total points
ID: 22944846
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

by:drweber
ID: 31516179
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

by:drweber
ID: 22945109
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

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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 SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question