Find percentage of column with where constraints in SQL Server

Question

So I have the following table format in SQL Server. I want to find the percentage of animals where the color is black.

Type    Color
--------------
Dog      Black  
Cat      Brown  
Dog      Brown  
Cat      Black  
Dog      white  
Dog      Black

So in other words the output would be something like this.

Dog  black  33%  
Cat  black  16.6%

How can I do this in a single query?

Possible duplicate of [How to calculate percentage with a SQL statement](http://stackoverflow.com/questions/770579/how-to-calculate-percentage-with-a-sql-statement) — Tab Alleman, Feb 23 '16 at 20:19

score 1 · Answer 1 · answered Feb 23 '16 at 19:49

1

SELECT [Type] 
     , Colour 
     , CAST( COUNT(*) * 100.00 /(Select COUNT(*) FROM TableName) AS DECIMAL(5,2))
FROM TableName
GROUP BY [Type] , Colour

answered Feb 23 '16 at 19:49

M.Ali

67,945
13
101
127

TT. · Answer 2 · 2016-02-23T20:00:48.230

You can do this by grouping on the type of animal, and getting the count for that group by COUNT(*) for that group. Divide that by the total number of animals ((SELECT COUNT(*) FROM #tt)).

SELECT* INTO #tt FROM (VALUES('Dog','Black'),('Cat','Brown'),('Dog','Brown'),('Cat','Black'),('Dog','white'),('Dog','Black')) AS t(Type,Color)

SELECT 
    Type,
    color='black',
    [percentage]=CAST(COUNT(*)*100.0/(SELECT COUNT(*) FROM #tt) AS DECIMAL(28,2))
FROM #tt
WHERE color='black'
GROUP BY type;

DROP TABLE #tt;

Prints out:

+------+-------+------------+
| Type | color | percentage |
+------+-------+------------+
| Cat  | black | 16.67      |
| Dog  | black | 33.33      |
+------+-------+------------+

Find percentage of column with where constraints in SQL Server

2 Answers2