Home / server / Questions / 235526
Accepted
Tie-fighter
Asked: 2011-02-15 17:46:27 +0800 CST

How to count unique values?

  • 772

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
sql postgresql

2 Answers

  1. Best Answer
    SELECT COUNT(DISTINCT ip_address) FROM bandits
    • 15

  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
    • 2