oracle query to count occurrences in multiple columns

I see that two individuals answered your question using aggregate examples. As in they are both using GROUP BY and the HAVING Clause. You don't necessarily need to use any sort of Grouping to get your desired output here. See below an alternate solution. It may be simply an opinion however I prefer this solution:

WITH demo_data (cname, col1, col2, col3) AS
( /* Using CTE to produce fake table with data */
  SELECT 'cust1', 'Y', 'Y', 'N' FROM dual UNION ALL
  SELECT 'cust2', 'N', 'N', 'Y' FROM dual UNION ALL
  SELECT 'cust3', 'Y', 'N', 'Y' FROM dual UNION ALL
  SELECT 'cust4', 'Y', 'Y', 'Y' FROM dual UNION ALL
  SELECT 'cust5', 'Y', 'Y', 'N' FROM dual UNION ALL
  SELECT 'cust6', 'Y', 'N', 'N' FROM dual
)
, transform_data AS
( /* Using decode to convert all 'Y' and 'N' to numbers */
  SELECT cname
       , decode(col1, 'Y', 1, 0) AS col1
       , decode(col2, 'Y', 1, 0) AS col2
       , decode(col3, 'Y', 1, 0) AS col3
  FROM demo_data
)
/* Now we created our SUM column using the columns 1 thru 3 */
SELECT cname, col1, col2, col3
  /* I didn't need to create the sum_col however I added it for visual purposes */
  , col1 + col2 + col3 AS sum_col
FROM transform_data
WHERE col1 + col2 + col3 > 1
;

Screenshot of the output for each of the tables produced by the WITH Clause and actual desired Output.

You can combine SUM() and HAVING() clause.

SELECT Customer_Name,
SUM(CASE WHEN Col1='Y' then 1 else 0 end)+
SUM(CASE WHEN Col2='Y' then 1 else 0 end)+
SUM(CASE WHEN Col3='Y' then 1 else 0 end) as total
FROM myTable
GROUP BY Customer_Name
HAVING SUM(CASE WHEN Col1='Y' then 1 else 0 end)+
SUM(CASE WHEN Col2='Y' then 1 else 0 end)+
SUM(CASE WHEN Col3='Y' then 1 else 0 end)=2

    Result:
    CUSTOMER_NAME   TOTAL
    Customer3        2
    Customer1        2

Somewhat similar to @anonyXmous' code, but using DECODE and not that many (unnecessary?) SUMs:

SQL> with test (cname, col1, col2, col3) as
  2    (select 'cust1', 'y', 'y', 'n' from dual union
  3     select 'cust2', 'n', 'n', 'y' from dual union
  4     select 'cust3', 'y', 'n', 'y' from dual
  5    )
  6  select cname,
  7    sum(decode(col1, 'y', 1, 0) +
  8        decode(col2, 'y', 1, 0) +
  9        decode(col3, 'y', 1, 0)) sumc
 10  from test
 11  group by cname
 12  having sum(decode(col1, 'y', 1, 0) +
 13             decode(col2, 'y', 1, 0) +
 14             decode(col3, 'y', 1, 0)) >= 2
 15  order by cname;

CNAME       SUMC
----- ----------
cust1          2
cust3          2

SQL>

From what you are showing it seems:

• You have a table with three boolean columns. As the Oracle DBMS still doesn't feature the boolean data type, you used col1 CHAR(1) NOT NULL, CONSTRAINT chk_customers_bool_col1 CHECK (col1 in ('Y','N')) etc. instead. (A pity that Oracle forces us to apply such workarounds.)
• You are showing a customer table with one record per customer and three attributes, that you call col1, col2, col3 in your example, but which are real attributes in real life, such as quick, friendly, rich, and you want to select customers for which at least two of the attributes are true.

One simple way to do this would be to concatenate the three letters, remove the Ns and count the remaining Ys.

select
  Customer_Name, 
  length(replace(col1 || col2 ||| col3 ,'N',''))
from customers
where length(replace(col1 || col2 ||| col3 ,'N','')) >= 2;

This query still works for three-state booleans (i.e. null allowed), but not if the columns can contain other charaters (such as 'M' for "maybe" or 'S' for "sometimes"). In that case you'd have to use a CASE expression or Oracle's DECODE to check for Ys.

Many people prefer the numbers 0 and 1 to emulate boolean false and true in Oracle. One advantage over 'N' and 'Y' is that you can quickly add them up to count how many are true, which is exactly your case. (In MySQL where a boolean data type exists, they even go so far as to allow math with them substituting false with 0 and true with 1, so true + false + true = 2.)