Computing a column that is a percentage of the sum of another column

[k4]

To keep things as simple as possible : I have a table with the
following information:

Unit1
Unit1
Unit2
Unit2
Unit2
Unit3

I want to return a result set that shows

Unit1 2 33%
Unit2 3 50%
Unit3 1 16%
Total 6 100%

Any suggestions?

[Plamen Ratchev]

Re: Computing a column that is a percentage of the sum of another column

Very similar to Dan's query, just using ROLLUP to add the summary row:

SELECT COALESCE(UnitNa me, 'Total') AS UnitName,
COUNT(*) AS cnt,
COUNT(*) * 100 /
(SELECT COUNT(*) FROM #Units) AS perc
FROM #Units
GROUP BY UnitName
WITH ROLLUP;

If UnitName can have NULL values then replace the use of COALESCE with the
GROUPING function which returns 1 if the row is added by ROLLUP.

HTH,

Plamen Ratchev

http://www.SQLStudio.com