-1

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

shree.pat18
  • 21,449
  • 3
  • 43
  • 63
cpd1
  • 777
  • 11
  • 31
  • Which DBMS? What have you tried that's not working? – shree.pat18 Jan 30 '15 at 02:38
  • I added to the title - it's Sybase IQ I just tried - count( role ) as a new column while joining the two tables – cpd1 Jan 30 '15 at 02:39
  • count(*) is only work without group by. – Ye Win Jan 30 '15 at 02:39
  • So what other options do I have for counting? Seems like I should be able to do it and sum() works as expected. Note - I also had to adjust the second table for a typo for manager count – cpd1 Jan 30 '15 at 02:41
  • @AndyD I have tagged your question with the appropriate DBMS. Please add this information into the tags rather than question title. – shree.pat18 Jan 30 '15 at 02:48
  • 1
    Any basic SQL reference will tell you that `COUNT` requires a `GROUP BY`. There is no exception to that requirement. If you have to obtain a count, you'll need to use a sub-query that has the `COUNT` with a `GROUP BY` the ID, and then `JOIN` that sub-query. – Ken White Jan 30 '15 at 03:11

1 Answers1

1

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.

Brian DeMilia
  • 13,103
  • 1
  • 23
  • 33