CODEWITHSUNDEEP
sqlsql-servert-sqljoincount
Oryx
Oryx

Reputation: 302

TSQL COUNT show 0 when no row returned

I have one table, Documents, that lists all documents and various information about them. What I'm trying to do is, for a select group of authors, get a count of all documents they've authored in the past year. For each document, we have a column to store the Author name and ID.

I am currently getting what I want with the query below, but my problem is I also need to list all authors that haven't authored any documents. (So, for the Number of Docs Signed column they'd have a zero value) Here's what I have now:

SELECT 
[AuthorID] As "Author ID",
RTRIM([AuthorFirstName]) + ' ' + RTRIM([AuthorLastName]) AS "Author", 
COUNT(Document.ID) AS "Number of Docs Authored"

FROM Document

WHERE   [CompletedStatus] = 'Yes' 
AND     [AuthorID] IN (<list of author ID's>) 
AND     [CompletedOn] >= DATEADD(d, -365, getdate())


GROUP BY [AuthorID], [AuthorFirstName], [AuthorLastName]

ORDER BY [Number of Docs Signed] DESC

From reading around on SO I know I think I need some kind of subquery that I can join in order to show a '0' when there are no rows returned by COUNT. But for the life of me I can't figure out how to do it. I'm pretty sure it needs to be something like this.

Upvotes: 2

Views: 3182

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

If you have an authors table, you would do:

SELECT  a.AuthorID As "Author ID",
        RTRIM([a.AuthorFirstName]) + ' ' + RTRIM([a.AuthorLastName]) AS "Author", 
        COUNT(Document.ID) AS "Number of Docs Authored"
FROM Authors a left outer join
     Document d
     on a.[AuthorID] = d.[AuthorID] and
        d.CompletedStatus = 'Yes' and
       [CompletedOn] >= DATEADD(d, -365, getdate())
WHERE a.[AuthorID] IN (<list of author ID's>) and
GROUP BY a.AuthorID, a.AuthorFirstName], a.authorLastName
ORDER BY [Number of Docs Signed] DESC

Note all the conditions on the document are going in the on clause. Otherwise, the values will be NULL for the Authors with no documents.

If you don't have an authors table, you can create one on the fly:

with authors as (
    select <id> as id, <firstname> as authorFirstName, <lastname> as authorLastName union all
    select . . .
)

And then use the above query. You need to fill in each author with the name information.

Upvotes: 2

FJT
FJT

Reputation: 2083

Presumably if they haven't authored any documents, they're not going to have a record in the document table. I guess you have an Authors table or similar?

Something like

SELECT 
a.[AuthorID] As "Author ID",
RTRIM(a.[AuthorFirstName]) + ' ' + RTRIM(a.[AuthorLastName]) AS "Author", 
COUNT(Document.ID) AS "Number of Docs Authored"
FROM Author a
LEFT OUTER JOIN
Document
ON A.AuthorId = Document.AuthorId
AND  [CompletedStatus] = 'Yes' 
AND     [CompletedOn] >= DATEADD(d, -365, getdate())
WHERE     A.[AuthorID] IN (<list of author ID's>) 
GROUP BY a.[AuthorID], a.[AuthorFirstName], a.[AuthorLastName]

Upvotes: 2

dotjoe
dotjoe

Reputation: 26940

Start from the Authors table and do a left outer join to the documents table. This means you need to move the criteria into the outer join...

SELECT 
[AuthorID] As "Author ID",
RTRIM([AuthorFirstName]) + ' ' + RTRIM([AuthorLastName]) AS "Author", 
COUNT(Document.ID) AS "Number of Docs Authored"

FROM 
    Author a
    LEFT OUTER JOIN Document d on d.AuthorID = a.ID
        AND [CompletedStatus] = 'Yes' 
        AND [CompletedOn] >= DATEADD(d, -365, getdate())
WHERE
    a.ID IN (<list of author ID's>) 

GROUP BY a.[ID], [AuthorFirstName], [AuthorLastName]

ORDER BY [Number of Docs Signed] DESC

Upvotes: 3

Related Questions