Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Count columns

SainaaOct 27 2020 — edited Oct 27 2020

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.
Screenshot 2020-10-26 173839.png

This post has been answered by Frank Kulash on Oct 27 2020
Jump to Answer

Comments

Frank Kulash
Answer

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?

Marked as Answer by Sainaa · Jan 27 2021
1 - 1

Post Details

Added on Oct 27 2020
1 comment
99 views