Need help in rewriting the query

Question

The original query is given below:

SELECT
     id
    ,COUNT(A_NO) AS count
FROM table1
WHERE date = '2022-02-02'
AND p_no IS NOT NULL
GROUP BY id; 

I need to rewrite the above query by not using p_no IS NOT NULL condition in the WHERE clause. So I tried the below 2 queries but they are taking long time to execute

SELECT
     id
    ,SUM(CASE WHEN A_NO IS NOT NULL AND p_no IS NOT NULL THEN 1 ELSE 0 END) AS count
FROM table1
WHERE date = '2022-02-02'
GROUP BY id;
    
    
SELECT
     id
    ,COUNT_IF(A_NO IS NOT NULL AND p_no IS NOT NULL) AS count
FROM table1
WHERE date = '2022-02-02'
GROUP BY id;

Is there any other way to rewrite the SQL query?

Answer 1

Your COUNT_IF seem pretty good.

but you can use NVL2, which returns the second or third value depending on the first null-ness, thus when p_no is not null count a_no else count NULL (which doesn't count)

SELECT
     id
    ,CASE(NVL2(p_no , a_no, NULL)) AS count
FROM table1
WHERE date = '2022-02-02'
GROUP BY id;

Order of next steps:

• If DATE is not a DATE/TIMESTAMP and is a VARCHAR/TEXT then store it as a real date.
• Order/Cluster you table/data by DATE
• Make the warehouse bigger.