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

Hello,

Not really sure how Counts, sum and procents work.

I have a table with a few rows like this

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

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

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

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

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

But do remember that divide by zero isn't allowed so if COUNT(X1) is zero you cannot attempt the percentage calculationsee: http://sqlfiddle.com/#!3/439ed/1

```
select (count(x1)*1.0 / count(1))*100 as PercentX1, (count(x2)*1.0 / count(1))*100 as PercentX2
from YourTableNameHere
```

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

The idea is not to return the percentage of rows that doesn't have NULL value for each column? Or I misunderstood?

>>"

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

All Courses

From novice to tech pro — start learning today.

The only way to simplify your query is if the values are only 0 and 1, so you can SUM them instead of counting:

Open in new window