I have a table i.e. Yearly_Author_CoAthors
having structure as:
Author | CoAuthor | Year
------------------------------
677 | 901706 | 2005
677 | 38459 | 2007
677 | 901706 | 2007
677 | 1695352 | 2007
677 | 901706 | 2009
677 | 372089 | 2011
677 | 403400 | 2011
677 | 478885 | 2011
677 | 478885 | 2012
677 | 42700 | 2013
677 | 625964 | 2013
1359 | 133112 | 2005
1359 | 1412785 | 2005
1359 | 151268 | 2006
1359 | 232222 | 2007
1359 | 264864 | 2007
...
...
I have executed a query as:
SELECT * FROM Yearly_Author_CoAuthors
WHERE CoAuthor = 901706
It gives output as:
Author | CoAuthor | Year
----------------------------
677 | 901706 | 2005
677 | 901706 | 2007
677 | 901706 | 2009
1683703 | 901706 | 2012
It means the CoAuthor = 901706
is associated with more than 1 Author
i.e. 677 and 1683703.
So the question is:
How can I modify this query to check all such CoAuthor
who associated with more than 1 Author
?
Using idea from Arulkumar, this query gave the expected result.
SELECT CoAuthor, COUNT(DISTINCT Author) [AuthorCount], Year
FROM Yearly_Author_CoAuthors
GROUP BY Year, CoAuthor
HAVING COUNT(DISTINCT Author) > 1
ORDER BY CoAuthor, Year