Equation in PostgreSQL only returns 0

Question

So i have these query to get the percentage of downtime that occurred that is below 95 but the problem this query only returns 0 which is i don't get why since the data that i'm putting has values. Please see query below:

SELECT ROUND(
    (
        (SELECT COUNT(*) AS terminal_down_count FROM dashboard.availability WHERE availability_percentage < 95 AND availability_date = '2020-04-05'
        ) / t.terminal_count
    ) * 100, 9)
FROM(
SELECT COUNT(*) AS terminal_count
FROM dashboard.availability
WHERE availability_date = '2020-04-05'
)t

If I highlight the query SELECT COUNT(*) AS terminal_down_count FROM dashboard.availability WHERE availability_percentage < 95 AND availability_date = '2020-04-05' This is what it will return:

While if I highlight this query SELECT COUNT(*) AS terminal_count FROM dashboard.availability WHERE availability_date = '2020-04-05 It will return this result:

So basically what i'm trying to return in the equation is something like this:

(146 / 4,272) * 100 = 3.42

But the overall query above it only outputs 0.

score 1 · Accepted Answer · answered May 23 '20 at 08:35

Try the following, convert terminal_down_count to float. so multiply with 1.0 as shown below or do cast to float. Here is the example.

SELECT ROUND(
    (
        (SELECT 
          COUNT(*) AS terminal_down_count 
          FROM dashboard.availability 
          WHERE availability_percentage < 95 
          AND availability_date = '2020-04-05'
        )*1.0 / t.terminal_count
    ) * 100, 9)
FROM(
SELECT COUNT(*) AS terminal_count
FROM dashboard.availability
WHERE availability_date = '2020-04-05'
)t

Equation in PostgreSQL only returns 0

1 Answers1