I thought I could count a column and add it as a column as I can with a sum but I get an error about having to group by / having. An example of what I want...
Initial table...
Global ID Local ID Name Role
100 1 Andy Manager
100 2 Andy Manager
100 1 John Co-Manager
200 1 Andy Co-Manager
200 2 John Manager
200 2 Mike Manager
I then want to add a column that counts the number of Manager in each group / local pairing...
Global ID Local ID Name Role Manager Count
100 1 Andy Manager 1
100 2 Andy Manager 1
100 1 John Co-Manager 0
200 1 Andy Co-Manager 0
200 2 John Manager 2
200 2 Mike Manager 2
I tried just joining the two tables on the global / local ID and then adding a column for count of the Role. The problem is that I get an error about grouping / having by but I don't want to group anything. I just want to add the column and still have the same number of rows. Any way around this?
FYI - for the last two rows, the last column has 2 because John and Mike are on the same group / local ID
It looks like your definition of a group is a unique combination of [global_id, local_id]. If that is the case you do want to group by those two values, and do a count, where the role is Manager. But because you want other columns from the original table, you must do that count within an inline view, and then join back to the original table, like so:
select t.*, v.mgr_in_grp
from tbl t
left join (select global_id, local_id, count(*) as mgr_in_grp
from tbl
where role = 'Manager'
group by global_id, local_id) v
on t.global_id = v.global_id
and t.local_id = v.local_id
Fiddle: http://sqlfiddle.com/#!2/fb3ace/2/0
Notice that there is a difference on row 3, as compared to your expected output.
John, Co-manager at global_id and local_id 100 and 1, respectively, belongs to a pair that has a manager in that combination (Andy, your first row). So the count appears should be 1.