Display Values even if COUNT = 0

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

Transact-SQL (2000)

Display Values even if COUNT = 0

Author

Topic

missshhhh
Starting Member

1 Post

Posted - 2006-12-31 : 04:32:00

I have a table called Tickets and a column called Type. The values of Type are Fault, Request and Inquiry.

My query is SELECT Count(*), Type FROM Tickets WHERE Type = "Fault" Group By Type. My problem is if there are no records in table Ticket that has a "Fault" type then no results will be displayed. I want that the query will display Fault 0. Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2006-12-31 : 04:50:20

[code]
SET NOCOUNT ON

Create Table #types (type varchar(8))

INSERT #t VALUES('Fault')
INSERT #t VALUES('Request')
INSERT #t VALUES('Inquiry')

SELECT t.type, isNull(count(a.type), 0)
FROM #types t
LEFT JOIN
tickets a
On a.type = t.type
WHERE t.type = 'Fault'
GROUP BY
t.type

Drop Table #types
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-01 : 03:29:18

Also refer http://weblogs.sqlteam.com/jeffs/archive/2005/09.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources