Just wanted to check if these two queries give same result or different result

user_16395449 0
Viewed 60

Query 1:

   SELECT id,COUNT(X.C_NO) as count
    FROM table X
    WHERE X.date = '2022-02-02'
    and P_NO is not null
    group by id;

Query 2:

SELECT   id,
         sum(CASE WHEN C_NO IS NOT NULL and P_NO is not null THEN 1 ELSE 0 END) as count
         FROM table X
WHERE B.date = '2022-02-02'
group by id;

Just wanted to know if both of these queries would produce the same results or different results

snowflake-schema snowflake-cloud-data-platform
1 Answers

These queries are not equivalent and they will produce different results. Example:

CREATE TABLE tab
AS
SELECT 1 AS id, NULL AS P_NO, '2022-02-02'::DATE AS date, 10 AS c_no


SELECT id,COUNT(X.C_NO) as count
FROM tab X
WHERE X.date = '2022-02-02'
  and P_NO is not null
group by id;

SELECT   id,
 sum(CASE WHEN C_NO IS NOT NULL and P_NO is not null THEN 1 ELSE 0 END) as count
FROM tab X
WHERE X.date = '2022-02-02'
group by id;

db<>fiddle demo

Output:

enter image description here

Key clause is WHERE clasue that is executed before GROUP BY. Some rows are filtered out before they even have chance to be grouped.

Sidenote:

The second query is an example of conditional aggregation, and could be further simplfied using COUNT_IF aggregate function:

SELECT id,
   COUNF_IF(C_NO IS NOT NULL and P_NO is not null) as count
FROM tab X
WHERE X.date = '2022-02-02'
group by id;
user_50708790
Related