CODEWITHSUNDEEP
sqlsql-server
Jordan Davis
Jordan Davis

Reputation: 875

Find percentage of column with where constraints in SQL Server

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?

Upvotes: 0

Views: 257

Answers (2)

TT.
TT.

Reputation: 16137

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      |
+------+-------+------------+

Upvotes: 0

M.Ali
M.Ali

Reputation: 69504

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

Upvotes: 1

Related Questions