Postgres: add unique integer to results?

Question

I'm using Postgres 9.6. I have a query like this:

SELECT name, count(spend) as spend FROM mytable GROUP BY name;

This produces results like Dave,40000.

I would like to add a unique identifier to each result (yes, I know the name is already a unique identifier, but I would like a numeric one). So I would like results like 1,Dave,40000.

It doesn't particularly matter what the unique identifiers are as long as they're numeric, but they might as well be integers.

Is there an easy way to do this with Postgres?

Clodoaldo Neto · Accepted Answer · 2017-03-03 19:46:43Z

4

A window function:

select row_number() over(order by name) as ui, name, count(spend) as spend
from mytable
group by name;

https://www.postgresql.org/docs/current/static/functions-window.html

answered Mar 3, 2017 at 19:46

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

Comments

Judith Palacios · Accepted Answer · 2017-03-03 19:52:19Z

1

you can use row_number

SELECT ROW_NUMBER () OVER (ORDER BY name) AS consecutivo, name, count(spend) as spend FROM mytable GROUP BY name;

answered Mar 3, 2017 at 19:52

Judith Palacios

4132 silver badges9 bronze badges

Collectives™ on Stack Overflow

Postgres: add unique integer to results?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related