InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 11:07:05 GMT20http://www.sqlservercentral.com/Forums/Topic812366-80-1.aspxGenerally speaking when the count is zero that record is skipped. I found a way to return the zero value by using Group by All and picking the column used in group by from a lookup table. My sql looks like the following and it works:SELECT UnitState AS UnitState, UnitState As State, Count(*) As [Pending Requests] FROM tLookup_StateTable LKState Left Join dbo.tbl_Status_Of_ContractRequests_By_State TBL on LKState.OrgCode = TBL.UnitState INNER JOIN dbo.tblContracts C ON C.Contract_ID = TBL.TrackingID(TBL.PENDING > 0) AND UnitState in (Select OrgCode From tLookup_StateTable LKState)Group By ALL UnitState, orgCode ORDER BY UnitStateThanks for helpingMon, 02 Nov 2009 09:47:08 GMTFaye Fouladihttp://www.sqlservercentral.com/Forums/Topic812366-80-1.aspxwithout more details, all i can offer is an example:in this case, I've wrapped the counting sql with another query to get me the isnull of the count.[code]SELECT ISNULL(CNT,0) As MatchingRecords FROM (SELECT COUNT(*) AS CNT FROM SYSOBJECTS WHERE 1=2 --CONDITION WILL NEVER FIND RECORDS) X[/code]the other thing to do is to use a SUM and CASE to calculate the count:[code]SELECT SUM(CASE WHEN xtype IN ('U') THEN 1 ELSE 0 END) AS TableCount, SUM(CASE WHEN xtype IN ('V') THEN 1 ELSE 0 END) AS ViewCount, SUM(CASE WHEN xtype IN ('P') THEN 1 ELSE 0 END) AS ProcCount, SUM(CASE WHEN xtype IN ('FN','TF') THEN 1 ELSE 0 END) AS FuncCount, SUM(CASE WHEN xtype IN ('bananas') THEN 1 ELSE 0 END) AS WierdCount --CONDITION WILL NEVER FIND RECORDS FROM SYSOBJECTS [/code]Mon, 02 Nov 2009 08:00:36 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic812366-80-1.aspxWhen trying to get the count of records where a certain condition is met and the no records are returned (no records meet the condition), how do I dispaly zero for that particualr group of records?Mon, 02 Nov 2009 07:28:21 GMTFaye Fouladi