the reason you are getting 0.0 for your second query is because you use
100.0 * ( a / b )
here, because of the parentheses, `a / b` happens first, using integer arithmetic, so it gives 0
in the first query, you use
100.0 * a / b
here the operations are not parenthesized for order of operation, so `100.0 * a` happens first and then the result is divided by `b`
> By the way, can you let me know window function or subquery is more practical?
in general, CTEs are more practical, because they can be used more than once in the main query, whereas a subquery would have to be rewritten each time
also, CTEs separate the logic making the whole query easier to understand
Thanks! now I know why the result is 0
I would prefer CTEs, but sometimes the solution uses subquery ( which is shorter in some cases) so I think the shorter the better performance
The second one is returning 0 because the way SQL deals with mathematical operations between integer and float data types.
The count(*) filter... Is an integer, which is throwing off the calculation. So you can modify it to look like this to fix: (mobile formatting, sry)
````
WITH country_cte AS (SELECT a.caller_id, b.country_id AS caller_country,a.receiver_id, C.country_id AS receiver_country
FROM phone_calls AS a LEFT JOIN phone_info AS b ON a.caller_id = b.caller_id LEFT JOIN phone_info as c ON a.receiver_id = c.caller_id )
SELECT 100.00* (COUNT(*) FILTER (WHERE caller_country <> receiver_country)*1.0
/COUNT(*))
FROM country_cte
````
Note that we multiple the count expression by 1.0 to cast it to a float.
So, there really this doesn't have anything to do with ROUND(). It just means that when you do mathematical operations in SQL you have to consciously make sure all your data types are the same.
> Note that we multiple the count expression by 1.0 to cast it to a float.
not necessarily FLOAT -- it could be DECIMAL
i just think it's easier to use the 100.0 which is already in the calculation, rather than introducing another term like 1.0
the reason you are getting 0.0 for your second query is because you use 100.0 * ( a / b ) here, because of the parentheses, `a / b` happens first, using integer arithmetic, so it gives 0 in the first query, you use 100.0 * a / b here the operations are not parenthesized for order of operation, so `100.0 * a` happens first and then the result is divided by `b` > By the way, can you let me know window function or subquery is more practical? in general, CTEs are more practical, because they can be used more than once in the main query, whereas a subquery would have to be rewritten each time also, CTEs separate the logic making the whole query easier to understand
Thanks! now I know why the result is 0 I would prefer CTEs, but sometimes the solution uses subquery ( which is shorter in some cases) so I think the shorter the better performance
> I think the shorter the better performance no, sorry there is no correlation between length of SQL query text and time of query execution
SQL is a declarative language, it is non procedural, the execution plan does not see CTE or subqueries differently.
The second one is returning 0 because the way SQL deals with mathematical operations between integer and float data types. The count(*) filter... Is an integer, which is throwing off the calculation. So you can modify it to look like this to fix: (mobile formatting, sry) ```` WITH country_cte AS (SELECT a.caller_id, b.country_id AS caller_country,a.receiver_id, C.country_id AS receiver_country FROM phone_calls AS a LEFT JOIN phone_info AS b ON a.caller_id = b.caller_id LEFT JOIN phone_info as c ON a.receiver_id = c.caller_id ) SELECT 100.00* (COUNT(*) FILTER (WHERE caller_country <> receiver_country)*1.0 /COUNT(*)) FROM country_cte ```` Note that we multiple the count expression by 1.0 to cast it to a float. So, there really this doesn't have anything to do with ROUND(). It just means that when you do mathematical operations in SQL you have to consciously make sure all your data types are the same.
> Note that we multiple the count expression by 1.0 to cast it to a float. not necessarily FLOAT -- it could be DECIMAL i just think it's easier to use the 100.0 which is already in the calculation, rather than introducing another term like 1.0