Post reply

When resultset of count is nothing

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

  • 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!

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply