PostgreSQL – Select Count of Specific Value Without Grouping

postgresql

I have a view that's a result of a UNION of two selects. It has contents similar to this:

id foreignkey
1  a
2  a
3  b
4  c

I'm looking to add a column in the view's definition that will show a count for the values, but without aggregating them. Somthing like this:

id foreignkey count
1  a          2
2  a          2
3  b          1
4  c          1

my existing query is basically:

create or replace view x as
select * from a
union
select * from b

I want the count on the result of the union but I"m not sure how to do that.

EDIT – made some progress, with a normal table I can do this:

SELECT *,
 (select count(id) from mytable where t.data = data)
FROM mytable as t

The problem comes into play because the table is really a union of two tables. Not sure how to do it without doing the union twice?

Best Answer

You can use a window function:

create or replace view x 
as
select id, foreignkey, count(*) over (partition by foreignkey) as fk_count
from (
  select id, foreignkey from a
  union
  select id, foreignkey from b
) x;

Unrelated, but:

UNION removes duplicates from the result, UNION ALL does not. If you know there are now duplicates, then you should be using UNION ALL because it's faster.

Related Question