When resultset of count is nothing

• Faye Fouladi

  SSCrazy

  Points: 2770

  More actions

  November 2, 2009 at 7:28 am

  #142874

  When 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?

• Lowell

  SSC Guru

  Points: 323522

  More actions

  November 2, 2009 at 8:00 am

  #1073816

  without 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.

  SELECT ISNULL(CNT,0) As MatchingRecords FROM

  (SELECT

  COUNT(*) AS CNT

  FROM SYSOBJECTS

  WHERE 1=2 --CONDITION WILL NEVER FIND RECORDS

  ) X

  the other thing to do is to use a SUM and CASE to calculate the count:

  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

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Faye Fouladi

  SSCrazy

  Points: 2770

  More actions

  November 2, 2009 at 9:47 am

  #1073883

  Generally 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 UnitState

  Thanks for helping