Sean McCarthy Sean McCarthy - 3 months ago 11
SQL Question

SQL formatting for percentage

Using the SQL code

select table.column, count(*) * 100.0 / sum(count(*)) over()
from table
group by table.column


I would like to use this function for its efficiency since I am working on a large DB. The function generates both values of percentage which sum to 100. I can't figure out a simple way to only generate the true value (1) or value of summed number over number of rows in the column. Is there a simple way I can do this or do I need to use a different function entirely ?

An example data set would be

N, Bit
0 | 0
1 | 0
2 | 1
3 | 0
4 | 0
5 | 1


It is a bit, null table where I am taking the percentage of true bits.
The N just stands for Number.

Answer

If you ONLY need to know the percentage of true bits, just do this:

SELECT COUNT(NULLIF(Bit, 0)) / CONVERT(Decimal, COUNT(*))
FROM Table

If you need to know the percentaje of true bits by other column (N in this case), you need something like this:

SELECT N, COUNT(NULLIF(Bit, 0)) / t2.C
FROM Table, (SELECT CONVERT(Decimal, COUNT(*)) C FROM Table) t2
GROUP BY N, C