Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

When resultset of count is nothing Expand / Collapse
Author
Message
Posted Monday, November 2, 2009 7:28 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:15 PM
Points: 114, Visits: 186
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?
Post #812366
Posted Monday, November 2, 2009 8:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:31 AM
Points: 12,901, Visits: 32,136
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #812380
Posted Monday, November 2, 2009 9:47 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:15 PM
Points: 114, Visits: 186
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
Post #812454
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse