CODEWITHSUNDEEP
postgresql
TheRealPapa
TheRealPapa

Reputation: 4539

Postgresql divide by zero caused by subquery

I am working on a project with Postgresql. I have a query where I am calculating lifetime averages for readings of devices.

If the device is new it may not have any data and it is causing divide by zero.

My query is:

COALESCE(
    (
        SELECT
            fl1.totaliser_kl
        FROM
            water_readings fl1
        WHERE
            fl1.meter_id = :meterId
        ORDER BY
            read_local_at DESC
        LIMIT
            1
    ) / (
        SELECT
            DATE_PART(
                'day',
                (
                    SELECT
                        fl3.read_local_at
                    FROM
                        water_readings fl3
                    WHERE
                        fl3.meter_id = :meterId
                    ORDER BY
                        fl3.read_local_at DESC
                    LIMIT
                        1
                ) :: TIMESTAMP - (
                    SELECT
                        fl4.read_local_at
                    FROM
                        water_readings fl4
                    WHERE
                        fl4.meter_id = :meterId
                    ORDER BY
                        fl4.read_local_at ASC
                    LIMIT
                        1
                ) :: TIMESTAMP
            )
    )
) AS avg_l_day

II have tried GREATEST and NULLIF functions around the divider sub query but I get syntax errors when it executes.

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "SELECT" LINE 13: SELECT ^

Any advice is appreciated

COALESCE(
    (
        SELECT
            fl1.totaliser_kl
        FROM
            water_readings fl1
        WHERE
            fl1.meter_id = :meterId
        ORDER BY
            read_local_at DESC
        LIMIT
            1
    ) / GREATEST(              <--- NULLIF / GREATEST 
        SELECT
            DATE_PART(
                'day',
                (
                    SELECT
                        fl3.read_local_at
                    FROM
                        water_readings fl3
                    WHERE
                        fl3.meter_id = :meterId
                    ORDER BY
                        fl3.read_local_at DESC
                    LIMIT
                        1
                ) :: TIMESTAMP - (
                    SELECT
                        fl4.read_local_at
                    FROM
                        water_readings fl4
                    WHERE
                        fl4.meter_id = :meterId
                    ORDER BY
                        fl4.read_local_at ASC
                    LIMIT
                        1
                ) :: TIMESTAMP
            )
    ), 1                      <--- ELSE DIVIDE BY 1
) AS avg_l_day

Upvotes: 0

Views: 87

Answers (1)

Bjarni Ragnarsson
Bjarni Ragnarsson

Reputation: 1781

You need to put parentheses () around the SELECT statement when used as an argument in GREATEST.

Upvotes: 2

Related Questions