Including Zero in Record Count

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Including Zero in Record Count

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2015-04-14 : 13:51:47

Hi, I would like to produce a record total for each user including those where the total is zero.

This produces what I need


SELECT U.UserID,
    SUM(CASE WHEN TL.ApprovalStatus = 'Completed' Then 1 Else 0 END) AS [TOP Completions]   
FROM  Region4Reports.dbo.Users AS U 
LEFT JOIN TOPLog.dbo.TOPLog AS TL ON U.UserID = TL.UserID
GROUP BY U.UserID

but I need to add a WHERE clause which then counts only records which match this criteria.

SELECT U.UserID,
    SUM(CASE WHEN TL.ApprovalStatus = 'Completed' Then 1 Else 0 END) AS [TOP Completions]   
FROM  Region4Reports.dbo.Users AS U 
LEFT JOIN TOPLog.dbo.TOPLog AS TL ON U.UserID = TL.UserID
WHERE TL.ApprovalStatus = 'Completed'
GROUP BY U.UserID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-14 : 13:59:38

I need to see sample data and expected result set to help.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

evanburen
Posting Yak Master

167 Posts

Posted - 2015-04-14 : 14:12:49

This is an example of the results I am getting now

These are the results that I would like to see

Thanks again

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-04-14 : 14:29:49

The first query you posted should give you the result with 0 rows in it.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-14 : 14:38:29

The second query is filtering so that you only get the Completed status, so that is why you are not getting the results you want

CREATE TABLE #T
( ID INT IDENTITY(1,1) , USERID int)

INSERT INTO #t
VALUES(1),(1),(1),(1),(1),(1),(1),(1)

UPDATE #t SET USERID = ID

CREATE TABLE #TT
( ID INT IDENTITY(1,1) , USERID int, ApprovalSTatus varchar(100))

INSERT INTO #TT
VALUES(1,'COMPLETED')

SELECT U.UserID,
SUM(CASE WHEN TL.ApprovalStatus = 'Completed' Then 1 Else 0 END) AS [TOP Completions]
FROM #t AS U
LEFT JOIN #TT AS TL ON U.UserID = TL.UserID
GROUP BY U.UserID

SELECT U.UserID,
SUM(CASE WHEN TL.ApprovalStatus = 'Completed' Then 1 Else 0 END) AS [TOP Completions]
FROM #t AS U
LEFT JOIN #TT AS TL ON U.UserID = TL.UserID
WHERE TL.ApprovalStatus = 'Completed' -- this is what is filtering your result
GROUP BY U.UserID

-- you could do the following but there really is no point . you'd be better off just using the first query as no one else has the completed status
SELECT U.UserID,
SUM(CASE WHEN TL.ApprovalStatus = 'Completed' Then 1 Else 0 END) AS [TOP Completions]
FROM #t AS U
LEFT JOIN #TT AS TL ON U.UserID = TL.UserID AND TL.ApprovalStatus = 'Completed'

GROUP BY U.UserID

evanburen
Posting Yak Master

167 Posts

Posted - 2015-04-14 : 14:51:57

Thanks very much. Your examples are teaching me a lot.

bonds22
Starting Member

1 Post

Posted - 2015-04-15 : 02:05:47

SELECT U.UserID,
SUM(CASE WHEN TL.ApprovalStatus = 'Completed' Then 1 Else 0 END) AS [TOP Completions]
FROM #t AS U
LEFT JOIN #TT AS TL ON U.UserID = TL.UserID
WHERE TL.ApprovalStatus = 'Completed' -- this is what is filtering your result
GROUP BY U.UserID

asad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources