9

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
1

2 Answers 2

15
SELECT COUNT(DISTINCT ip_address) FROM bandits
1
  • 1
    wow, thank you, I was trying SELECT DISTINCT COUNT[...] etc. Sometimes I'm just unseeing... Feb 15, 2011 at 2:07
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .