COUNT with WHERE clause giving more rows than without WHERE clause

Question

This may not be the right forum to ask this but I want to understand the logical error happening in my query.

I have wrote below query to understand how many users have delivered messages greater than sent messages(possibly an error in data capture, just wanted to assess it).

SELECT COUNT(DISTINCT user_id)
FROM wk_24_trigger

UNION

SELECT COUNT(DISTINCT user_id)

FROM (

SELECT *, (CASE WHEN delivered > 0 THEN 1 ELSE 0 END) as D,
(CASE WHEN sent > 0 THEN 1 ELSE 0 END) as S
FROM wk_24_trigger) t
WHERE t.D > t.s

The result which I got are as belows

    _c0
1   1056840
2   1819729

I am not getting why row 2 > row 1.

Ideally even if for every entry Delivered > Sent then row 2 and row 1 should have been same

Answer 1

Are you sure that the first row is the result from the first query and the second one from the second query..??

It always need not be..

Try adding alias name after the count in each query and verify the result..

you can check below example as well..

WITH TEMP
AS(
SELECT 'A' USER_ID , 1 DELIVERED  , NULL SENT  FROM DUAL
UNION 
SELECT 'B' ID , 10 A , 1 B FROM DUAL
UNION 
SELECT 'C' ID , NULL A , 1 B FROM DUAL
UNION 
SELECT 'D' ID , -1 A , 1 B FROM DUAL
) 
SELECT COUNT(DISTINCT USER_ID), 'QUERY_1' QUERY
FROM TEMP
UNION
(SELECT COUNT(DISTINCT USER_ID), 'QUERY_2'

  FROM (        
        SELECT USER_ID,DELIVERED,SENT,
                (CASE
                  WHEN DELIVERED > 0 THEN
                   1
                  ELSE
                   0
                END)  D,
                (CASE
                  WHEN SENT > 0 THEN
                   1
                  ELSE
                   0
                END)  S
          FROM TEMP) T
 WHERE T.D > T.S);

and system output is as below..

    COUNT(DISTINCTUSER_ID)  QUERY
1                       1   QUERY_2
2                       4   QUERY_1

the same could be your case as well..