CODEWITHSUNDEEP
sqlpostgresql
user776942
user776942

Reputation:

Select distinct column and get count of another column

I have the following sqlfiddle:

CREATE TABLE tester(
  name TEXT,
  address TEXT
 )

Each person in the table can have multiple addresses. I'd like to select all names and the number of addresses they have that have > 1 address. I have tried:

SELECT d.name, count(address) c FROM (SELECT DISTINCT ON(name) FROM tester) d
  LEFT JOIN tester ON d.name = links.name
  WHERE count(address) > 1

I get:

ERROR: syntax error at or near "FROM" Position: 64

I've also tried a DISTINCT ON query:

SELECT DISTINCT ON(name) name, count(address) FROM tester HAVING count(address) > 1

I get:

ERROR: column "tester.name" must appear in the GROUP BY clause or be used in an aggregate function Position: 26

I feel like I'm making this too difficult.

Upvotes: 0

Views: 64

Answers (2)

Hogan
Hogan

Reputation: 70513

You just need to use group by correctly. Like this:

SELECT name, count(*) 
FROM tester
group by name

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 220762

Simply use GROUP BY:

SELECT name, count(address)
FROM tester
GROUP BY name
HAVING count(address) > 1

GROUP BY in SQL (as well as in other languages) will always produce distinct groups, so there is no need for DISTINCT in this case.

Upvotes: 3

Related Questions