CODEWITHSUNDEEP
sqlcloudera
Anna
Anna

Reputation: 464

ratio calculated in Cloudera

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?

Upvotes: 2

Views: 18

Answers (1)

Aaron Dietz
Aaron Dietz

Reputation: 10277

You can use 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(*) includes NULLs in the total count, so nothing special required there.

Upvotes: 2

Related Questions