sql - ratio calculated in Cloudera

Question

How can I get the ratio from a variable that has one of 0, 1, NULL to make sure the NULLs are counted as 0 and I get the the ratio of all

sum(COALESCE(call_received, 0)) AS call_received

The snippet above I think should take care of the NULLs, what should I divide it by to make sure I take everything into account?

标签： sqlcloudera

Answer 1

您可以使用COUNT(*)：

SELECT SUM(COALESCE(call_received,0)) AS call_received,
       COUNT(*) AS call_total,
       SUM(COALESCE(call_received,0)) / COUNT(*) AS call_received_ratio
FROM yourTable

COUNT(*)包括NULLs 在总数中，所以那里没有什么特别的要求。