[SOLVED] Select Count Including Zeros Counts

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 2005 Forums

Transact-SQL (2005)

[SOLVED] Select Count Including Zeros Counts

Author

Topic

mkswanson
Starting Member

21 Posts

Posted - 2011-10-09 : 23:15:56

Hello,

I have a SQL select statement that is working as desired.

SELECT     TOP (100) PERCENT dbo.surveyUser.surveyUserID, COUNT(DISTINCT dbo.response.responseID) AS surveyCount, dbo.V_userDetails.firstName, 
                      dbo.V_userDetails.lastName, dbo.location.locationName
FROM         dbo.surveyUser INNER JOIN
                      dbo.mgrRelationship ON dbo.surveyUser.surveyUserID = dbo.mgrRelationship.associateUserID INNER JOIN
                      dbo.V_userDetails ON dbo.surveyUser.surveyUserID = dbo.V_userDetails.surveyUserID INNER JOIN
                      dbo.location ON dbo.V_userDetails.surveyAssignedLocation = dbo.location.locationID LEFT OUTER JOIN
                      dbo.response ON dbo.surveyUser.surveyUserID = dbo.response.surveyUserID
WHERE     (dbo.surveyUser.isSurveyTech = 1) AND (dbo.mgrRelationship.isAllowed = 1) AND (dbo.mgrRelationship.surveyUserID = 283)
GROUP BY dbo.surveyUser.surveyUserID, dbo.V_userDetails.firstName, dbo.V_userDetails.lastName, dbo.location.locationName
ORDER BY dbo.location.locationName, dbo.V_userDetails.lastName

When I add a date parameter to this, it no longer includes the entries of surveyUserID where the count is zero. I want to include zero entries, as well.

SELECT     TOP (100) PERCENT dbo.surveyUser.surveyUserID, COUNT(DISTINCT dbo.response.responseID) AS surveyCount, dbo.V_userDetails.firstName, 
					  dbo.V_userDetails.lastName, dbo.location.locationName 
FROM		 dbo.surveyUser INNER JOIN 
					  dbo.mgrRelationship ON dbo.surveyUser.surveyUserID = dbo.mgrRelationship.associateUserID INNER JOIN 
					  dbo.V_userDetails ON dbo.surveyUser.surveyUserID = dbo.V_userDetails.surveyUserID INNER JOIN 
                      dbo.location ON dbo.V_userDetails.surveyAssignedLocation = dbo.location.locationID LEFT OUTER JOIN 
					  dbo.response ON dbo.surveyUser.surveyUserID = dbo.response.surveyUserID 
WHERE 	  (dbo.surveyUser.isSurveyTech = 1) AND (dbo.mgrRelationship.isAllowed = 1) AND (dbo.mgrRelationship.surveyUserID = 283) 
AND (dbo.response.serviceDate > '09/09/2011 00:00:00') AND (dbo.response.serviceDate < '10/09/2011 23:59:59') 
GROUP BY dbo.surveyUser.surveyUserID, dbo.V_userDetails.firstName, dbo.V_userDetails.lastName, dbo.location.locationName 
ORDER BY dbo.location.locationName, dbo.V_userDetails.lastName

Is it possible to include the zero count entries with the date requirement?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-10 : 01:24:52

[code]
SELECT TOP (100) PERCENT dbo.surveyUser.surveyUserID, COUNT(DISTINCT dbo.response.responseID) AS surveyCount, dbo.V_userDetails.firstName,
dbo.V_userDetails.lastName, dbo.location.locationName
FROM dbo.surveyUser INNER JOIN
dbo.mgrRelationship ON dbo.surveyUser.surveyUserID = dbo.mgrRelationship.associateUserID INNER JOIN
dbo.V_userDetails ON dbo.surveyUser.surveyUserID = dbo.V_userDetails.surveyUserID INNER JOIN
dbo.location ON dbo.V_userDetails.surveyAssignedLocation = dbo.location.locationID LEFT OUTER JOIN
dbo.response ON dbo.surveyUser.surveyUserID = dbo.response.surveyUserID
AND (dbo.response.serviceDate > '09/09/2011 00:00:00') AND (dbo.response.serviceDate < '10/09/2011 23:59:59')
WHERE (dbo.surveyUser.isSurveyTech = 1) AND (dbo.mgrRelationship.isAllowed = 1) AND (dbo.mgrRelationship.surveyUserID = 283)
GROUP BY dbo.surveyUser.surveyUserID, dbo.V_userDetails.firstName, dbo.V_userDetails.lastName, dbo.location.locationName
[/code]
ORDER BY dbo.location.locationName, dbo.V_userDetails.lastName

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mkswanson
Starting Member

21 Posts

Posted - 2011-10-13 : 14:30:44

Awesome...and it worked!

Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 00:57:18

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources