How to count unique values?

Question

I am trying get the number of unique ip_addresses (in this case '3'). The table looks like this:

Structure:

CREATE TABLE bandits (
  key text NOT NULL,
  ip_address inet,
  offence text,
  count bigint DEFAULT 1);

Data:

COPY bandits (key, ip_address, offence, count) FROM stdin;
127.0.0.1_testing   127.0.0.1  testing  1
127.0.0.2_testing   127.0.0.2  testing  3
127.0.0.2_testing2  127.0.0.2  testing2 1
127.0.0.3_testing   127.0.0.3  testing  1

Try `select distinct .....` – John Gardeniers Feb 15 '11 at 01:50 — John Gardeniers, Feb 15 '11 at 01:50

score 15 · Accepted Answer · edited Aug 09 '17 at 09:53

15

SELECT COUNT(DISTINCT ip_address) FROM bandits

edited Aug 09 '17 at 09:53

Frederik Struck-Schøning

117
4

answered Feb 15 '11 at 02:00

Mark Henderson

68,823
31
180
259

1

wow, thank you, I was trying SELECT DISTINCT COUNT[...] etc. Sometimes I'm just unseeing... – Tie-fighter Feb 15 '11 at 02:07

score 2 · Answer 2 · edited Aug 09 '17 at 11:34

2

As mentioned here: https://stackoverflow.com/questions/11250253/postgresql-countdistinct-very-slow, it could be much faster to use a bit longer version instead:

SELECT count(*) FROM (SELECT DISTINCT ip_address FROM bandits) AS bandits_distinct

edited Aug 09 '17 at 11:34

Frederik Struck-Schøning

117
4

answered Mar 04 '16 at 22:10

icl7126

171
5

How to count unique values?

2 Answers2