foo.sql

-- original query without the aggregation
SELECT o.OpinionDocumentId,
       o.DisplayTitle,
       o.Date
FROM dbo.OpinionDocumentOpinionDocuments c
JOIN dbo.OpinionDocuments o ON o.OpinionDocumentId = c.OpinionDocument_OpinionDocumentId
WHERE c.OpinionDocument_OpinionDocumentId1 = 617143

-- adjusted query with aggregation sub-query
-- can this query be improved?
SELECT o.OpinionDocumentId,
       o.DisplayTitle,
       o.Date,
       (SELECT COUNT(1)
        FROM dbo.OpinionDocumentOpinionDocuments
        WHERE OpinionDocument_OpinionDocumentId1 = o.OpinionDocumentId) AS CitedByCount
FROM dbo.OpinionDocumentOpinionDocuments c
JOIN dbo.OpinionDocuments o ON o.OpinionDocumentId = c.OpinionDocument_OpinionDocumentId
WHERE c.OpinionDocument_OpinionDocumentId1 = 617143