Sean McCarthy - 4 months ago 19

SQL Question

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