Need help in rewriting the query

February 21, 2022

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?

>Solution :

Your COUNT_IF seem pretty good.

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.