postgres concat in group by

Question

One question about Postgresql selects. This works as it should:

SELECT 
  name,SUM(cash)
FROM 
  costumers
GROUP BY (name)

but how can I concat two (or more) fields in the GROUP BY clause? This is what I tried:

SELECT 
  name,SUM(cash)
FROM 
  costumers
GROUP BY (name || ' ' || nickname)

What is the error message that you got?

Mark Byers
– Mark Byers

2012-08-21 19:36:43 +00:00
Commented Aug 21, 2012 at 19:36 — Mark Byers
– Mark Byers, Commented Aug 21, 2012 at 19:36

Mark Byers · Accepted Answer · 2012-08-21 19:33:07Z

3

That will work, except that you need to select the expression you group by:

SELECT 
    (name || ' ' || nickname) AS name_and_nickname,
    SUM(cash) AS total_cash
FROM costumers
GROUP BY (name || ' ' || nickname)

Another option is to group by two fields by separating them with a comma:

SELECT 
    name, nickname, SUM(cash) AS total_cash
FROM costumers
GROUP BY name, nickname

Note that these two are not exactly equivalent. In particular these two rows will end up in the same group with the first version and in different groups in the second version:

name    | nickname  | cash
--------+-----------+----
foo     | bar baz   | 10
foo bar | baz       | 20

The second option is probably what you mean.

answered Aug 21, 2012 at 19:33

Mark Byers

843k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anatol Over a year ago

wow, thanks a million! this seems to be what i was looking for. I tried both, you wrote these two are not exactly the same. is the difference: the first select, outputs the name and nickname as two coloums. your second example combines the name and nickname coloum to one coloum. if so, this was exactly what i was looking for :)

Mark Byers Over a year ago

@tBook: That is also a minor difference, but it's not the one I was referring to. The difference I meant is that the two rows I have shown end up in different groups with the second query, but in the same group with the first query. If there cannot be spaces in the name or nickname then this is not a problem.

Collectives™ on Stack Overflow

postgres concat in group by

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related