CODEWITHSUNDEEP
postgresql
Richard
Richard

Reputation: 65560

Postgres: add unique integer to results?

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?

Upvotes: 0

Views: 55

Answers (2)

Judith Palacios
Judith Palacios

Reputation: 413

you can use row_number

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

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

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

Upvotes: 4

Related Questions