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(%)

Thanks in advance!
drweberAsked:
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.

Kevin CrossChief 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
0
Kevin CrossChief 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

Open in new window

0
drweberAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Kevin CrossChief 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.
0
drweberAuthor 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
0
drweberAuthor Commented:
Anyone else out there have an idea on this?
0
SharathData 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
0
drweberAuthor 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)
0
drweberAuthor Commented:
Sorry, forgot to answer, each category is already unique, done in a previous step
0
SharathData 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  
0
Kevin CrossChief 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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
drweberAuthor 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
0
Kevin CrossChief 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

Open in new window

0
drweberAuthor 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
0
SharathData 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  
0
SharathData 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  
0
Kevin CrossChief 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

Open in new window

0
drweberAuthor 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?
0
SharathData EngineerCommented:
Did you check my query?
0
drweberAuthor 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
0
SharathData 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  
0
drweberAuthor 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
0
SharathData 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    
0
drweberAuthor 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.
0
drweberAuthor 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

Open in new window

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.