Count columns

Sainaa

Hello I have a list of columns with different channels and total counts.

Total counts = channel 1 count + channel 2 count .... etc

What I want to do is I want to count columns with values higher than 0 meaning

tot_cnt(10) = 0 + 0 +2 +3 +0 +5 + 0

It should count 3 be this user used 3 kind of channels.

Now second problem is I want to divide tot_cnt with total channels used meaning

10/3 = 3~4

Now I want to find users with similar average on all the channels meaning

user with 0+ 0 +0 +3 + 3 + 4 + 0 qualifies while

user with 0 + 5 + 1 + 4 + 0 + 0 +0 disqualifies because difference is too big.

Frank Kulash

Hi,

Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data. Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

See: https://community.oracle.com/tech/apps-infra/kb/articles/4-how-to-ask-questions-in-developer-community-spaces

If col1, col2, col3, ... are NUMBER columns (or any kind of NUMBER expressions) you can find how many of them that are positive numbers like this:

CASE WHEN col1 > 0 THEN 1 ELSE 0 END +
CASE WHEN col2 > 0 THEN 1 ELSE 0 END +
CASE WHEN col3 > 0 THEN 1 ELSE 0 END ...

If all the expressions are 0 or greater (i.e. no NULLs or negative values) then you can use:

SIGN (col1) +
SIGN (col2) +
SIGN (col3) ...

You can reduce the amount of code by unpivoting the columns, but that will make the query slower. Which is more important in this case: concise code or performance?

Frank Kulash

Hi,

Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data. Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

See: https://community.oracle.com/tech/apps-infra/kb/articles/4-how-to-ask-questions-in-developer-community-spaces

If col1, col2, col3, ... are NUMBER columns (or any kind of NUMBER expressions) you can find how many of them that are positive numbers like this:

CASE WHEN col1 > 0 THEN 1 ELSE 0 END +
CASE WHEN col2 > 0 THEN 1 ELSE 0 END +
CASE WHEN col3 > 0 THEN 1 ELSE 0 END ...

If all the expressions are 0 or greater (i.e. no NULLs or negative values) then you can use:

SIGN (col1) +
SIGN (col2) +
SIGN (col3) ...

You can reduce the amount of code by unpivoting the columns, but that will make the query slower. Which is more important in this case: concise code or performance?