Skip to content

Instantly share code, notes, and snippets.

@ryancole
Last active August 29, 2015 14:15
Show Gist options
  • Save ryancole/383e34f44297a9462215 to your computer and use it in GitHub Desktop.
Save ryancole/383e34f44297a9462215 to your computer and use it in GitHub Desktop.
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment