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?

  • please make a minimal reproducible example wuth wanted result
    – nbk
    Mar 21 at 16:47
  • @ypercubeᵀᴹ - I looked at this - was going to answer... Can you explain why the results from this fiddle are different to those of this fiddle? Does PostgreSQL create a "pseudo" PRIMARY KEY behind the scenes if one isn't explicitly created ? MySQL does this for example...
    – Vérace
    Mar 21 at 17:08
  • Yes, the "distinct" made the trick. If you answer the question I'll mark it as solved.Thanks!
    – Wonton
    Mar 21 at 21:41
  • @Vérace: I added an explanation for non-deterministic results to my answer. Mar 22 at 0:13

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
   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:

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
   SELECT DISTINCT ON (user_id) city
   FROM   cities
   ORDER  BY user_id, city  -- making the pick determinisitic
   ) sub

Which is equivalent to:

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

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);      
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),                                                                                                                                                                                                                                                                   
  from cities                                                                                                                                                                                                                                                                   
  group by username                                                                                                                                                                                                                                                           
select cities.username,                                                                                                                                                                                                                                                         
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,                                                                                                                                                                                                                                                  
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!

  • 1
    No need for all this JOINing - the PostgreSQL DISTINCT ON construct will take care of this for you! See here...
    – Vérace
    Mar 21 at 17:02
  • That's great, thank you. I think you should write it as an answer as it is far better than mine.
    – Arkhena
    Mar 21 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.