1

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
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Seventh
  • 23
  • 3
  • Sample data would be helpful here. – Tim Biegeleisen Sep 22 '19 at 01:02
  • If you want "all the columns" to accompany an aggregated field, you'll have to define from which of the aggregated rows you want to pick additional columns. And to get the optimal query you must also disclose Postgres version, table definition, row count and roughly how many rows per `hash_value` are to be expected? – Erwin Brandstetter Sep 22 '19 at 01:25

3 Answers3

0

I am assuming that you are getting duplicate columns when you say: "but there are duplicates hash_value being displayed"

select q.hash_value, q.total, ttt.field1, ttt.field2, ttt.field3
from top_teams_team ttt
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
Shaunak Sontakke
  • 980
  • 1
  • 7
  • 17
0

Try using COUNT as an analytic function:

SELECT *, COUNT(*) OVER (PARTITION BY hash_value) total
FROM top_teams_team;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

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:

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • This did the trick! Thank you so much. I'm a total noob at Postgres, so I was trying to figure out this all day. I appreciate the help very much. – Seventh Sep 22 '19 at 02:04