count even when zero - 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

General SQL Server Forums

New to SQL Server Programming

count even when zero

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-08-14 : 17:19:28

I want to get all the companies with type counts even when it's zero. result:
------------------------------------------------------------------------------
Auto Racing Request 0
Auto Racing Fault 0
Auto Racing Inquiry 1

Auto Spring Request 0
Auto Spring Fault 1
Auto Spring Inquiry 0

------------------------------------------------------------------------------

drop table tickets
go
DROP TABLE #types
go

CREATE TABLE tickets (TYPE VARCHAR(8), CompanyName varchar(50))
INSERT tickets
SELECT 'Fault', 'Auto Spring' UNION ALL
SELECT 'Inquiry', 'Auto Racing'

Create Table #types (type varchar(8))
INSERT #types VALUES('Fault')
INSERT #types VALUES('Request')
INSERT #types VALUES('Inquiry')

SELECT fa.CompanyName , fet.type, isNull(count(fa.type), 0) cnts
FROM tickets fa
right JOIN
#types fet
On fa.type = fet.type
GROUP BY fet.type, fa.CompanyName

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-14 : 17:40:57

Here is one way:

SELECT 
T.CompanyName , T.type, COUNT(fet.type) AS  cnts
FROM
(
	SELECT 
		Ticket.CompanyName
		,Type.type
	FROM
		tickets AS Ticket
	CROSS JOIN
		#Types AS Type
) AS T
LEFT OUTER JOIN
	tickets fa 
	ON T.CompanyName = fa.CompanyName
LEFT OUTER JOIN 
	#types fet
	On T.type = fet.type
	AND fa.TYPE = fet.type
GROUP BY 
	T.type, T.CompanyName

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-08-15 : 16:39:16

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources