how to apply if else in a group by query PostgreSQL

Question

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

1. config->>'camera_http_port'
2. config->>'external_host'
3. 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.

Answer 1

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
) ;