GROUP BY, but use only one row per user

Question

I've spent several hours to make a "simple" SELECT with GROUP BY in Postgres without success. The GROUP BY clause is giving me problems.

I have the table cities with columns user_id and city. user_id can be repeated, so the table can have this information:

"Bill", "New York"
"Bill", "Chicago"
"Adam", "New York"
"Mike", "Los Angeles"
...

If I would like to have the count of cities it could be make this way:

SELECT cities.city, COUNT(*) FROM cities GROUP BY cities.city

But if I want to have this count and take only one city per user (it doesn't matter if "Bill" takes "New York" or "Chicago") how could I group by cities.user_id?

Answer 1

Your query does not exactly do a count of cities, but rather the count of users per listed city. To get that after de-duplicating users:

SELECT city, count(*) AS users
FROM  (
   SELECT DISTINCT ON (user_id) city
   FROM   cities
   ) sub
GROUP  BY city;

This picks one row per user_id arbitrarily like you specified. So we need no ORDER BY in the inner SELECT.

We need nothing but the city from the inner query for the bare count.

Detailed explanation for DISTINCT ON:

• Select first row in each GROUP BY group?

Not deterministic for arbitrary pick

The above is typically fastest for few rows per user_id, while implementing stated requirements.

But the result is not deterministic while we pick rows arbitrarily. Can return different numbers for repeated executions as Postgres is free to pick any row for one user. (The total sum over all cities is stable, though, being the count of users.)

The result is normally stable, but any change to the table can trigger a different result. Like autovacuum doing its job in the background, or any unrelated write operation on the table.

To get deterministic results you need to add a deterministic ORDER BY to the inner query, so that DISTINCT ON always picks the same row. Like:

SELECT city, count(*) AS users
FROM  (
   SELECT DISTINCT ON (user_id) city
   FROM   cities
   ORDER  BY user_id, city  -- making the pick determinisitic
   ) sub
GROUP  BY 1;

Which is equivalent to:

SELECT city, count(*) AS users
FROM  (
   SELECT min(city)
   FROM   cities
   GROUP  BY user_id
   ) sub
GROUP  BY 1;

Answer 2

It seems like you don't understand well the group by clause... Maybe this link can help.

If you have a primary key in your cities tables, you can use it to get the first (or last, your choice) row by username:

laetitia=# create table cities(id integer generated always as identity primary key,                                                                                                                                                                                           
  username text,                                                                                                                                                                                                                                                              
  cityname text);      
CREATE TABLE
laetitia=# insert into cities (username, cityname) values ('bill', 'New York');                                                         
INSERT 0 1      
laetitia=# insert into cities (username, cityname) values ('bill', 'Chivago');
INSERT 0 1   
laetitia=# insert into cities (username, cityname) values ('adam', 'New York');                                                         
INSERT 0 1              
laetitia=# insert into cities (username, cityname) values ('mike', 'Los Angeles');                                                      
INSERT 0 1          
laetitia=# update cities set cityname='Chicago' where cityname = 'Chivago';                                                             
UPDATE 1           
laetitia=# table cities
laetitia-# ;
 id | username |  cityname                                                                                                                                                                                                                                                    
----+----------+-------------                                                                                                          
  1 | bill     | New York       
  3 | adam     | New York
  4 | mike     | Los Angeles                                                                                                                                                                                                                                                    2 | bill     | Chicago      
(4 rows)        
laetitia=# with firstline(id, username) as (                                                                                                                                                                                                                                    
  select min(id),                                                                                                                                                                                                                                                                   
    username                                                                                                                                                                                                                                                                    
  from cities                                                                                                                                                                                                                                                                   
  group by username                                                                                                                                                                                                                                                           
)                                                                                                                                                                                                                                                                             
select cities.username,                                                                                                                                                                                                                                                         
  cityname                                                                                                                                                                                                                                                                    
from cities                                                                                                                                                                                                                                                                     
  inner join firstline                                                                                                                                                                                                                                                            
  on cities.id = firstline.id                                                                                                                                                                                                                                               
;
 username |  cityname   
----------+-------------
 bill     | New York
 adam     | New York
 mike     | Los Angeles
(3 rows)

If you don't have a primary key (not good) or don't want to use it, you can take the first cityname by alphabetical order for each user with that kind of query:

laetitia=# select c1.username,                                                                                                                                                                                                                                                  
  c1.cityname,                                                                                                                                                                                                                                                                  
  c2.cityname                                                                                                                                                                                                                                                                 
from cities c1                                                                                                                                                                                                                                                                  
  left join cities c2                                                                                                                                                                                                                                                             
    on c1.username = c2.username                                                                                                                                                                                                                                                  
    and c1.cityname > c2.cityname                                                                                                                                                                                                                                             
where c2.cityname is null                                                                                                                                                                                                                                                     
;
 username |  cityname   | cityname 
----------+-------------+----------
 adam     | New York    | 
 mike     | Los Angeles | 
 bill     | Chicago     | 
(3 rows)

Have a nice day!