I have a table of project issues that looks like:

issueID  author    owner  assignee
-------  ------    -----  --------
1        jon       fred   fred
2        mary      fred   ralph
3        yak       ralph  jane
4        ralph     yak    jane
etc

What I'm after is the summary counts for each person, in each role. Eg for the above data I'd like:

person  numAuthored  numOwned  numAssigned
------  -----------  --------  -----------
jon     1            0         0
fred    0            2         1
mary    1            0         0
ralph   1            1         1
yak     1            1         0
jane    0            0         2

The nearest I can get (by a series of select into/count statements, one for each field to summate) is:

person  numAuthored  numOwned  numAssigned
------  -----------  --------  -----------
jon     1            NULL      NULL
mary    1            NULL      NULL
ralph   1            NULL      NULL
yak     1            NULL      NULL
fred    NULL         2         NULL
ralph   NULL         1         NULL
yak     NULL         1         NULL
fred    NULL         NULL      1
ralph   NULL         NULL      1
jane    NULL         NULL      2


But this has duplicates in the person that somehow need to be 'collapsed' together.

Else there is a much better way of doing this?

TIA

Steve"