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!

, 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