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?