query syntax

Hello,

Not really sure how Counts, sum and procents work.

I have a table with a few rows like this

id   x1       x2
1    null     0
2    1         1
3    1         null
4    0         null

Open in new window


where i am looking for the procentage of rows that have a 1 in the x1 and x2 columns.  Null rows should not be included.

so x1 procent should be 66% and x2 should be 50%

I am having trouble because i am not sure if there is a standard function that will do this, or what the most efficient way would be if one does not exist.

Its important that it is efficient because we are talking about a lot of rows and many more columns.

/thanks
soozhAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Ok. I can see that I misunderstood the question.
The only way to simplify your query is if the values are only 0 and 1, so you can SUM them instead of counting:
select (sum(x1)*1.0 / count(1))*100 as PercentX1, (sum(x2)*1.0 / count(1))*100 as PercentX2
from YourTableNameHere

Open in new window

0
 
PortletPaulCommented:
SELECT
      COUNT(CASE WHEN x1 = 1 THEN 1 END)                   AS x1_1
    , COUNT(x1)                                            AS x1
    , CASE WHEN COUNT(x1) > 0 THEN 
           COUNT(CASE WHEN x1 = 1 THEN 1 END) * 100.0 / COUNT(x1)
           ELSE 0 
      END                                                  AS x1_pct
    , COUNT(CASE WHEN x2 = 1 THEN 1 END)                   AS x2_1
    , COUNT(x2)                                            AS x2
    , CASE WHEN COUNT(x2) > 0 THEN 
           COUNT(CASE WHEN x2 = 1 THEN 1 END) * 100.0 / COUNT(x2)
           ELSE 0
      END                                                 AS x2_pct
FROM table1
;

Open in new window

The aggregate function COUNT()  will only count non-null values (in other words it ignores nulls)
So the result of the query above is:
| X1_1 | X1 |          X1_PCT | X2_1 | X2 | X2_PCT |
|------|----|-----------------|------|----|--------|
|    2 |  3 | 66.666666666666 |    1 |  2 |     50 |

Open in new window

But do remember that divide by zero isn't allowed so if COUNT(X1) is zero you cannot attempt the percentage calculation

see: http://sqlfiddle.com/#!3/439ed/1
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
soozh, check if this works and isn't slow:
select (count(x1)*1.0 / count(1))*100 as PercentX1, (count(x2)*1.0 / count(1))*100 as PercentX2
from YourTableNameHere

Open in new window

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
PortletPaulConnect With a Mentor Commented:
In case it isn't obvious, I included more columns than you requested so you could follow the calculations. The unwanted columns can be removed. e.g.
SELECT
      CASE WHEN COUNT(x1) > 0 THEN 
           COUNT(CASE WHEN x1 = 1 THEN 1 END) * 100.0 / COUNT(x1)
           ELSE 0 
      END                                                  AS x1_pct
    , CASE WHEN COUNT(x2) > 0 THEN 
           COUNT(CASE WHEN x2 = 1 THEN 1 END) * 100.0 / COUNT(x2)
           ELSE 0
      END                                                 AS x2_pct
FROM table1
;

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
PortletPaul, why are you not counting the zeros?
The idea is not to return the percentage of rows that doesn't have NULL value for each column? Or I misunderstood?
0
 
PortletPaulCommented:
because that is required, and non-null values of x1 or x2 are to be counted.

>>"so x1 procent should be 66% and x2 should be 50%"


X1 has 3 non-null values, 2 of those = 1 hence: 2/3 = 66%
x2 has 2 non-null values, 1 of those = 1 hence: 1/2 = 50%

you are counting all records through count(1) [ it's the same as count(*)] which is 4
you are then counting x1 or x2

so you get:
3 values of x1 / 4 rows = 75%
2 values of x2 / 4 rows = 50%

try it here: http://sqlfiddle.com/#!3/439ed/2
0
All Courses

From novice to tech pro — start learning today.