CODEWITHSUNDEEP
sqlpostgresql
Junaid Farooq
Junaid Farooq

Reputation: 2608

PostgreSQL - SELECT duplicate data depending on 3 columns

This is the table.

CREATE TABLE cameras
(
  id integer NOT NULL DEFAULT nextval('sq_streams'::regclass),
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now(),
  exid text NOT NULL,
  owner_id integer NOT NULL,
  is_public boolean NOT NULL,
  config json NOT NULL,
  name text NOT NULL,
  last_polled_at timestamp with time zone DEFAULT now(),
  is_online boolean,
  timezone text,
  last_online_at timestamp with time zone DEFAULT now(),
  location geography(Point,4326),
  mac_address macaddr,
  model_id integer,
  discoverable boolean NOT NULL DEFAULT false,
  preview bytea,
  thumbnail_url text,
  CONSTRAINT pk_streams PRIMARY KEY (id)
)

I have 5000 records in this. What i am trying to get is the result below : There are 57 Cameras and they have everything different but 3 things are the same, which are 

'external_http_port', config->> 'external_host', config-> 'snapshots'->> 'jpg'

I want to create a query which will show me results as 

external_http_port | external_host | config-> 'snapshots'->> 'jpg' | Count
"93.87.73.118"     | "1024"        |      "/oneshotimage.jpg"      | 57
"180.180.180.48"   |  "81"         |"/onvif/media_service/snapshot"|20

Upvotes: 1

Views: 87

Answers (2)

Andy K
Andy K

Reputation: 5085

Would it do the trick? (Think as an analogy, instead of litterally)

CREATE TABLE stacktest
(col1 VARCHAR(8),
col2 VARCHAR(8),
col3 VARCHAR(8))


INSERT INTO stacktest VALUES ('A','B','C')
INSERT INTO stacktest VALUES ('A','B','C')
INSERT INTO stacktest VALUES ('A','B','C')
INSERT INTO stacktest VALUES ('A','B','C')
INSERT INTO stacktest VALUES ('A','B','C')
INSERT INTO stacktest VALUES ('A','B','C')
INSERT INTO stacktest VALUES ('C','A','B')
INSERT INTO stacktest VALUES ('C','A','B')
INSERT INTO stacktest VALUES ('C','A','B')


SELECT col1,col2,col3 
FROM stacktest
GROUP BY  col1,col2,col3
HAVING (COUNT(col1)>3 AND COUNT(col2) >3 AND COUNT(col3) > 3)

Upvotes: 0

Tom-db
Tom-db

Reputation: 6878

what about this?

SELECT config ->>'external_http_port' AS external_http_port, 
       config->> 'external_host' AS external_host, 
       config-> 'snapshots'->> 'jpg' AS snapshots,
       count(*) 
FROM cameras  
GROUP BY config->>'external_http_port', config->> 'external_host', config-> 'snapshots'->> 'jpg' 
HAVING count(*) > 1;

Upvotes: 1

Related Questions