Percentage of results that have a column in SQL

Question

I am trying to get a results that will show Uniuque "Reasons", the number of them there are, and the percentage of the total that they are. so far I have

SELECT DISTINCT Reason, 
       COUNT(Reason) AS Number, 
       CAST(COUNT(Reason) AS float) / CAST(COUNT(*) AS float) AS percentage
FROM DeletedClients

However as I have discovered COUNT(*) and COUNT(Reason) give the same result. So my basic question is, how do i get the total number of rows when I am using distinct in the query? I am using SQL server 2005

Answer 1

select reason, count(reason), 
       (count(reason)::float / (select count(reason) from reasons)::float) * 100 
       as percent 
from DeletedClients group by reason order by reason;

This is what I came up with. I did the casting in a postgresql specific manner but you can adjust that for your needs. You need to use group by. I added the order by just because I liked it :P

Answer 2

I think you should use group by:

SELECT Reason, 
       COUNT(Reason) AS Number, 
       CAST(COUNT(Reason) AS float) / CAST(COUNT(*) AS float) AS percentage
FROM DeletedClients
GROUP BY Reason

Answer 3

Use:

SELECT x.reason,
       x.num AS NUMBER,
       CONVERT(x.num, float)/(SELECT CONVERT(COUNT(*), float) FROM DELETEDCLIENTS) AS PERCENTAGE
  FROM (SELECT t.reason,
               COUNT(*) 'num'
          FROM DELETEDCLIENTS t
      GROUP BY t.reason) x

Answer 4

Your query will have problems as it is. There is no group by (which you need, if you want to return the Reason column.

As for the goal, COUNT (and other aggregates) ignore nulls (if you're using SQL Server at least), so this should work:

DECLARE @TOTAL FLOAT
SELECT @TOTAL = COUNT(1)
FROM DeletedClients

SELECT DISTINCT
    Reason,
    Cnt AS Number,
    @TOTAL AS TotalRecords,
    CAST(Cnt AS float) / @TOTAL AS percentage
FROM (
    SELECT
        Reason,
        COUNT(*) AS Cnt
    FROM DeletedClients
    GROUP BY Reason
) t
GROUP BY
    Reason,
    Cnt

Answer 5

SELECT Reason, 
       COUNT(Reason) AS Number, 
       CAST(COUNT(Reason) AS float) / CAST(t.Total AS float) AS percentage
FROM DeletedClients,
     (SELECT COUNT(*) As Total FROM DeletedClients) t
GROUP BY Reason, Total