CODEWITHSUNDEEP
sqlpostgresqlgroup-by
add-semi-colons
add-semi-colons

Reputation: 18810

Postgres: Group by on a column but query needs to return all columns in the table

I have table that has following structure: 

id, user_id, connected_id, completed
1, 2345, 232, t
2, 2345, 121, f
3, 4324, 123, t
4, 4324, 121, t
5, 1211, 134, f
6, 1211, 111, t

Is there a way to do get all columns of the table group by user_id. Since column that I group by has to be in the clause. Is there a work around.

I was trying to construct following result set:

user_id, total_completed

I thought I would do a two stage process, since I am actually accessing data via mybatis.

Upvotes: 0

Views: 94

Answers (3)

jbyler
jbyler

Reputation: 7847

When you use GROUP BY, the output columns must either be columns that you grouped on or can be aggregation functions. To understand why, think about grouping by user_id in your example data. If you try to output the column connected_id, what value should that column contain in the row for user_id=2345? There are two values, 232 from the first row and 121 from the second row. You must choose how to handle the multiple values. You have several options:

  • Add connected_id to the GROUP_BY clause. This will give you one row of output for user_id=2345, connected_id=232 and one row of output for user_id=2345, connected_id=121
   user_id  connected_id

   2345     232       
   2345     121       
   4324     123       
   4324     121       
   1211     134       
   1211     111
  • Use the aggregation function array_agg(connected_id). This will give you all the values as an array:
    user_id  array_of_connected_id

    2345     {232, 121}
    4324     {123, 121}
    1211     {134, 111}
  • Use a math function like max(connected_id):
    user_id  max_of_connected_id

    2345     232
    4324     123
    1211     134

Since there can be multiple values, you have to be explicit about how to handle those multiple values. For your specific case, the answer from @zerkms will give what you're looking for.

Upvotes: 2

zerkms
zerkms

Reputation: 254926

Seems like what you need is

  SELECT user_id,
         COUNT(CASE WHEN completed THEN 1 END) total_completed
    FROM tablename
GROUP BY user_id

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269883

I think you want distinct on:

select distinct on (user_id) t.*
from mytable t
order by user_id;

This will return an arbitrary row for each user_id.

Upvotes: 1

Related Questions