How to select all columns and count from a table?

Question

I'm trying to select all columns in table top_teams_team as well as get a count of values for the hash_value column. The sql statement here is partially working in that it returns two columns, hash_value and total. I still want it to give me all the columns of the table as well.

select hash_value, count(hash_value) as total
from top_teams_team
group by hash_value

In the sql statement below, it gives me all the columns, but there are duplicates hash_value being displayed which isn't what I want. I tried putting distinct keyword in but it wasn't working correctly or maybe I'm not putting it in the right place.

select *
from top_teams_team
inner join (
    select hash_value, count(hash_value) as total
    from top_teams_team
    group by hash_value
) q
on q.hash_value = top_teams_team.hash_value

Answer 1

A combination of a window function with DISTINCT ON might do what you are looking for:

SELECT DISTINCT ON (hash_value)
       *, COUNT(*) OVER (PARTITION BY hash_value) AS total_rows
FROM   top_teams_team
-- ORDER  BY hash_value, ???
;

DISTINCT ON is applied after the window function, so Postgres first counts rows per distinct hash_value before picking the first row per group (incl. that count).

The query picks an arbitrary row from each group. If you want a specific one, add ORDER BY expressions accordingly.

This is not "a count of values for the hash_value column" but a count of rows per distinct hash_value. I guess that's what you meant.

Detailed explanation:

• Best way to get result count before LIMIT was applied
• Select first row in each GROUP BY group?

Depending on undisclosed information there may be (much) faster query styles ...

• Optimize GROUP BY query to retrieve latest row per user