Lets assume that the table below is called Table
**---------------------------------------------
ID Col1 Col2 Col3 Col4 ... Total
--------------------------------------------
1 1 0 NULL 1 30.33
2 0 1 1 1 60.12
3 1 1 0 0 20.12
4 1 0 1 1 60.12
5 0 NULL NULL 1 10.19
6 1 1 NULL 1 90.00
7 0 0 NULL 0 0.00
--------------------------------------------**
I wanted to count and get the average number of columns that has a "true" in it. And display the total average of it in the Total Columns. For example there are 10 columns and 5 columns are true so I divide it and got 50% in total. Assuming that all of the columns that I will counting are bit and has a value of null,0 and 1. How do I achieve this one?
You could use:
SELECT
ID,
100.0*(COALESCE(Col1, 0) + COALESCE(Col2, 0) + ... + COALESCE(Col10, 0)) / 10 AS pct
FROM yourTable;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments