Postgres - using DISTINCT ON to get first row, but also getting the COUNT(*) work

Question

I'm using

SELECT DISTINCT ON()

to return a particular row from a group of rows. Works well. But what I also want to return is the "COUNT(*)". So it might look like

SELECT DISTINCT ON(name)
  name, num_items, COUNT(name)
FROM customers
ORDER BY name, num_items DESC

But of course, I get an error saying "name should be in a GROUP BY aggregate function".

How can I achieve the same result with the "count()" included?

roman · Accepted Answer · 2013-09-18 05:36:04Z

4

you can use count as window function:

select distinct on (name)
    name, num_items, count(*) over(partition by name)
from customers
order by name, num_items desc;

or in your case you can just use group by name and use simple aggregation:

select
    name, max(num_items), count(*)
from customers
group by name
order by name

answered Sep 18, 2013 at 5:36

roman

118k30 gold badges205 silver badges209 bronze badges

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

1 Answer 1