CODEWITHSUNDEEP
postgresql
wonder
wonder

Reputation: 903

Postgres : Get multiple columns with group by

Table

select * from hello;
 id | name 
----+------
  1 | abc
  2 | xyz
  3 | abc
  4 | dfg
  5 | abc
(5 rows)

Query

select name,count(*) from hello where name in ('abc', 'dfg') group by name;

 name | count 

------+-------

 dfg  |     1
 abc  |     3
(2 rows)

In the above query, I am trying to get the count of the rows whose name is in the tuple. However, I want to get the id as well with the count of the names. Is there a way this can be achievable? Thanks

Upvotes: 0

Views: 42

Answers (2)

ravioli
ravioli

Reputation: 3823

If you want to return the "id" values, then you can use a window function:

select id, name, count(*) over(PARTITION BY name)
from hello 
where name in ('abc', 'dfg');

This will return the id values along with the count of rows per name.

Upvotes: 2

user330315
user330315

Reputation:

If you want to see all IDs for each name, you need to aggregate them: 

select name, count(*), array_agg(id) as ids
from hello 
where name in ('abc', 'dfg') 
group by name;

This returns something like this:

name | count | ids    
-----+-------+--------
abc  |     3 | {1,3,5}
dfg  |     1 | {4}

Upvotes: 1

Related Questions