- 3,715,603 Users
- 2,242,809 Discussions
- 7,845,458 Comments
Forum Stats
Discussions
Categories
- 16 Data
- 362.2K Big Data Appliance
- 7 Data Science
- 1.6K Databases
- 465 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 22 Multilingual Engine
- 487 MySQL Community Space
- 3 NoSQL Database
- 7.6K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 416 SQLcl
- 42 SQL Developer Data Modeler
- 184.8K SQL & PL/SQL
- 21K SQL Developer
- 1.9K Development
- 3 Developer Projects
- 32 Programming Languages
- 135.1K Development Tools
- 8 DevOps
- 3K QA/Testing
- 247 Java
- 5 Java Learning Subscription
- 10 Database Connectivity
- 66 Java Community Process
- 1 Java 25
- 9 Java APIs
- 141.1K Java Development Tools
- 6 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 10 Java SE
- 13.8K Java Security
- 3 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 125 LiveLabs
- 30 Workshops
- 9 Software
- 3 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 3 Deutsche Oracle Community
- 10 Español
- 1.9K Japanese
- 2 Portuguese
Count columns

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.
Best 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).
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?
Answers
-
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).
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?