CODEWITHSUNDEEP
mysql
Saint Robson
Saint Robson

Reputation: 5525

Sub-Query Gives Me Different Result

I have this query :

SELECT *
FROM gross_profit
WHERE client_name = 'john'
AND timestamp = 'lm153-1531439443738'

and here's the result :

+--------+-------------+---------------------+-------------+------------------+------------------+
| id     | client_name | timestamp           | profit_loss | profit_loss_unit | profit_loss_type |
+--------+-------------+---------------------+-------------+------------------+------------------+
| 500392 | john        | lm153-1531439443738 |  1.90000000 | ADA              | profit           |
| 500393 | john        | lm153-1531439443738 |  0.00210900 | USDT             | fraction         |
| 500394 | john        | lm153-1531439443738 |  0.00000000 | BTC              | normal           |
| 509189 | john        | lm153-1531439443738 | -1.43713843 | ADA              | fee              |
+--------+-------------+---------------------+-------------+------------------+------------------+
4 rows in set (0.01 sec)

now, I want to eliminate any timestamp which has profit_loss_type = fee.

so I modify my query into this :

SELECT *
FROM gross_profit
WHERE client_name = 'john'
AND timestamp = 'lm153-1531439443738'
GROUP BY timestamp
HAVING SUM(CASE WHEN profit_loss_type = 'fee' THEN 1 ELSE 0 END) = 0

and of course the result : Empty set (0.01 sec)

but why, when I use that query as sub-query :

SELECT * FROM (
    SELECT timestamp
    FROM gross_profit
    WHERE client_name = 'john'
    AND profit_loss > 0
    GROUP BY timestamp
    HAVING SUM(CASE WHEN profit_loss_type = 'fee' THEN 1 ELSE 0 END) = 0
) AS t1
WHERE t1.timestamp = 'lm153-1531439443738'

it gives me : 1 row in set (0.60 sec) and lm153-1531439443738 shows as result.

this really confuse me. why the result is different when I use as a single query and as sub-query?

Upvotes: 1

Views: 30

Answers (1)

Vincent
Vincent

Reputation: 1614

I think this is because of the WHERE condition :

AND profit_loss > 0

which is executed before the HAVING condition.

Then, the "fee" profit_loss type is filtered_out before, and your result is not empty any more.

Try to remove this line.

Upvotes: 1

Related Questions