Display 0 if no results? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Display 0 if no results?

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-07-20 : 16:46:54

In this query, there are no records for 108A, is it possible to display 108A 0 0 ?

SELECT SUM(Tot_Dollars) AS OrderTotal, User_Def_Fld_3, COUNT(Tot_Dollars) as OrderCount
FROM OEHDRHST_SQL
WHERE Ord_dt >= 20040701
AND Ord_Type = 'O'
AND User_Def_Fld_3 IN ('108A', '107B', '107C')
GROUP BY User_Def_Fld_3

Kristen
Test

22859 Posts

Posted - 2004-07-20 : 17:02:11

Add
[code]
UNION ALL
SELECT 0, '108A', 0
WHERE NOT EXISTS(SELECT * FROM ... WHERE ...)
[code]
Kristen
where the "... WHERE ..." represents the opposite of the main SELECT

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-20 : 17:56:05

Or something like this:


SELECT 
	SUM(ISNULL(os2.Tot_Dollars,0)) AS OrderTotal, 
	os1.User_Def_Fld_3, 
	COUNT(ISNULL(os2.Tot_Dollars,0)) as OrderCount
FROM 
	OEHDRHST_SQL os1
	LEFT JOIN OEHDRHST_SQL os2 ON os1.UserDefFld_3 = os2.UserDefFld_3 --This should be the primary key actually. 
WHERE 
	os1.Ord_dt >= 20040701
	AND os1.Ord_Type = 'O'
	AND os1.User_Def_Fld_3 IN ('108A', '107B', '107C')
GROUP BY 
	os1.User_Def_Fld_3