how to apply if else in a group by query PostgreSQL

Question

I am creating a view on the basis of three column values

config->>'camera_http_port'
config->>'external_host'
config->'snapshots'->>'jpg'

The whole view query seems like this

create or replace view duplicated_cameras_report as
(select
    count(nullif(c.is_online = false, true)) as online,
    c.config->>'camera_http_port' as camera_http_port,
    c.config->>'external_host' as external_host,
    LOWER(config->'snapshots'->>'jpg') as jpg,
    count(nullif(cr.status like 'off', 'on')) as is_recording,
    count(*) as count
from
    cameras c
left join cloud_recordings cr
    on c.id = cr.camera_id
group by camera_http_port, external_host, jpg
having
    count(*) > 1)

We have added a new column in table cameras which is recording_from_nvr boolean.

also new config as config->'snapshots'->>'nvr_jpg' and config->>'nvr_http_port'

I want to generate the same view but for all those rows where recording_from_nvr is true, I want to group by config->'snapshots'->>'nvr_jpg' instead of config->'snapshots'->>'jpg' and config->>'nvr_http_port' instead of config->>'camera_http_port'

and where recording_from_nvr is false, then group by config->'snapshots'->>'jpg' and config->>'camera_http_port'.

I don't know if that's possible or not. Please guide me in case this can be done in some other way, the purpose of the view is to eliminate those cameras where

camera_http_port, external_host and 'snapshots'->>'jpg' are same

and

if recording_from_nvr = true, and nvr_http_port, external_host and 'snapshots'->>'nvr_jpg' are same.

Thanks in advance.

ypercubeᵀᴹ · Accepted Answer · 2021-10-09 06:44:18Z

The easiest way might be to use a UNION of two queries, one having WHERE recording_from_nvr and the other WHERE NOT recording_from_nvr - and different GROUP BY clauses, accordingly to your specs.

Example:

create or replace view duplicated_cameras_report as
(
-- first case, WHERE recording_from_nvr = TRUE
select
    count(nullif(c.is_online = false, true)) as online,
    c.config->>'nvr_http_port' as http_port,
    c.config->>'external_host' as external_host,
    LOWER(c.config->'snapshots'->>'nvr_jpg') as jpg
    count(nullif(cr.status like 'off', 'on')) as is_recording,
    count(*) as count
from
    cameras c
left join cloud_recordings cr
    on c.id = cr.camera_id
where c.recording_from_nvr
group by http_port, external_host, jpg
having
    count(*) > 1

UNION ALL

-- second case, WHERE recording_from_nvr = FALSE
select
    count(nullif(c.is_online = false, true)) as online,
    c.config->>'camera_http_port' as http_port,
    c.config->>'external_host' as external_host,
    LOWER(c.config->'snapshots'->>'jpg') as jpg,
    count(nullif(cr.status like 'off', 'on')) as is_recording,
    count(*) as count
from
    cameras c
left join cloud_recordings cr
    on c.id = cr.camera_id
where NOT c.recording_from_nvr
group by http_port, external_host, jpg
having
    count(*) > 1
) ;

Stack Exchange Network

how to apply if else in a group by query PostgreSQL

1 Answer 1

Your Answer

Hot Network Questions

how to apply if else in a group by query PostgreSQL

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions