November 2, 2009 at 7:28 am
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
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
November 2, 2009 at 9:47 am
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy