First of all please accept my apologize because of bad english. Also I'm new to this portal. I hope you'll help me for my questions.

I have two questions in Oracle.

1. How to find the number of '*' in each column on the table.
2. how to find the total number of '*' in the table.

• The first question answer will be 2 3 4
• The second one answer will be 7.

Thanks in advance to spend your valuable time to answer my questions!

C1  C2  C3
*   1   1
0   0   *
1   *   *
*   *   *

CodePudding user response：

select sum(
case when c1='*' then 1
else 0 end) c1_cnt,
sum(
case when c2='*' then 1
else 0 end) c2_cnt,
sum(
case when c3='*' then 1
else 0 end) c3_cnt,
sum(
regexp_count(c1||c2||c3,'\*')) cnt_total_stars
from cnt_star;

For the original table -

select * from cnt_star;

CodePudding user response：

Use conditional aggregation to count the stars in each column and for the total stars just add the previous totals:

SELECT COUNT(CASE c1 WHEN '*' THEN 1 END) AS c1_stars,
       COUNT(CASE c2 WHEN '*' THEN 1 END) AS c2_stars,
       COUNT(CASE c3 WHEN '*' THEN 1 END) AS c3_stars,
       COUNT(CASE c1 WHEN '*' THEN 1 END)
         COUNT(CASE c2 WHEN '*' THEN 1 END)
         COUNT(CASE c3 WHEN '*' THEN 1 END) AS total_stars
FROM   table_name