CODEWITHSUNDEEP
sqlpostgresqlselectcountaggregate-functions
arty
arty

Reputation: 669

How to find columns that only have one value - Postgresql

I have 2 tables, person(email, first_name, last_name, postcode, place_name) and location(postcode, place_name). I am trying to find people that live in places where only one person lives. I tried using SELECT COUNT() but failed because I couldn't figure out what to count in this situation.

SELECT DISTINCT email,
                first_name,
                last_name
FROM person
INNER JOIN location USING(postcode,
                          place_name)
WHERE 1 <=
    (SELECT COUNT(?))

Upvotes: 1

Views: 564

Answers (3)

peter.petrov
peter.petrov

Reputation: 39457

I would do this as follows. I find it plain and simple. 

select p1.* from 
person p1
join 
(
    select p.postcode, p.place_name, count(*) cnt from 
    person p
    group by p.postcode, p.place_name
) t on p1.postcode = t.postcode and p1.place_name = t.place_name and t.cnt = 1

How does it work?
In the inner query (aliased t) we just count how many people live in each location. Then we join the result of it (t) with the table person (aliased p1) and in the join we require t.cnt = 1. This is probably the most natural way of doing it, I think.

Upvotes: 1

arty
arty

Reputation: 669

Thanks to the help of people here, I found this answer:

SELECT first_name,
       last_name,
       email
FROM person
WHERE postcode IN
    (SELECT postcode
     FROM person
     GROUP BY postcode,
              place_name
     HAVING COUNT(place_name)=1
     ORDER BY postcode)
  AND place_name IN
    (SELECT place_name
     FROM person
     GROUP BY postcode,
              place_name
     HAVING COUNT(postcode)=1
     ORDER BY place_name)

Upvotes: 0

Andronicus
Andronicus

Reputation: 26046

Aggregate functions always go with having:

SELECT DISTINCT first_value(email) over (partition by place_name),
                first_value(first_name) over (partition by place_name),
                first_value(last_name) over (partition by place_name),
                count(*)
FROM person
INNER JOIN location USING(postcode,
                          place_name)
GROUP BY place_name
HAVING count(*) = 1

For more about the window functions (like first_value) check out this tutorial.

Upvotes: 1

Related Questions