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
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 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?


You can use a window function:

create or replace view x 
select id, foreignkey, count(*) over (partition by foreignkey) as fk_count
from (
  select id, foreignkey from a
  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.

| improve this answer | |
  • Worked great, thanks! I had to add an alias to the subquery though but otherwise perfect. – Jesse McMillan Jun 16 '19 at 2:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.