MSSQL - Count(*) Skipping 0 - 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

Transact-SQL (2000)

MSSQL - Count(*) Skipping 0

Author

Topic

velkymx
Starting Member

5 Posts

Posted - 2005-12-13 : 16:29:56

OK - basically I am trying to build a view that will count all of the records in a specific category. While it is returning lists with counts >0 it is just skipping the 0.

dbo.Patient is what we are counting, and ReportStatus are the categories.

HELP!

<code>

SELECT COUNT(dbo.Patient.PatientID) AS LeadCount, MONTH(dbo.Patient.CreateDate) AS CreateMonth, YEAR(dbo.Patient.CreateDate) AS CreateYear,

dbo.SourceCodes.Name AS Category

FROM dbo.Patient INNER JOIN

dbo.Status ON dbo.Patient.StatusID = dbo.Status.StatusID RIGHT OUTER JOIN

dbo.SourceCodes ON dbo.Patient.SourceID = dbo.SourceCodes.SourceID

WHERE (dbo.Status.ReportStatusID IN (4, 5, 6, 7))

GROUP BY MONTH(dbo.Patient.CreateDate), YEAR(dbo.Patient.CreateDate), dbo.SourceCodes.Name

</code>

So it is returning:

TV 19 3/2005
-- 3 4/2005
Internet 27 4/2005
Other 6 4/2005
Print 14 4/2005
Radio 1 4/2005
Referral 68 4/2005
TV 32 4/2005

Instead of
-- 0 3/2005
Internet 0 3/2005
Other 0 3/2005
Print 0 3/2005
Radio 0 3/2005
Referral 0 3/2005
TV 19 3/2005
-- 3 4/2005
Internet 27 4/2005
Other 6 4/2005
Print 14 4/2005
Radio 1 4/2005
Referral 68 4/2005
TV 32 4/2005

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-13 : 16:50:55

Put your sourceCodes table first then LEFT JOIN to the other tables:


create table #data (rowid int identity(1,1), lookupid int)
create table #lookup (lookupid int, lookupDesc varchar(20))
go
insert #lookup
select 1, 'item 1' union
select 2, 'item 2' union
select 3, 'item 3'

insert #data (lookupid)
select 1 union all
select 1 union all
select 2 union all
select 2 union all
select 2
go

select	l.lookupDesc
	,count(d.lookupid) [count]
from	#lookup l
left join #data d
		on d.lookupid = l.lookupid
group by l.lookupDesc
order by l.lookupDesc

go
drop table #lookup
drop table #data

Be One with the Optimizer
TG

velkymx
Starting Member

5 Posts

Posted - 2005-12-13 : 16:57:33

OK so I updated to this... still doesnt work

SELECT dbo.SouceCategories.SourceCategory AS Category, COUNT(*) AS LeadCount, MONTH(dbo.tmpGLSource.CreateDate) AS CreateMonth,
YEAR(dbo.tmpGLSource.CreateDate) AS CreateYear
FROM dbo.SouceCategories LEFT OUTER JOIN
dbo.tmpGLSource ON dbo.tmpGLSource.Name = dbo.SouceCategories.SourceCategory
GROUP BY dbo.SouceCategories.SourceCategory, MONTH(dbo.tmpGLSource.CreateDate), YEAR(dbo.tmpGLSource.CreateDate),
dbo.tmpGLSource.ReportStatusID
HAVING (dbo.tmpGLSource.ReportStatusID IN (4, 5, 6, 7))

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-13 : 17:10:43

Does this work? If not, post the DDL and inserts for some sample, and exptected results and we'll get it working for you.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


SELECT	dbo.SouceCategories.SourceCategory AS Category
	,COUNT(dbo.tmpGLSource.Name) AS LeadCount
	,MONTH(dbo.tmpGLSource.CreateDate) AS CreateMonth
	,YEAR(dbo.tmpGLSource.CreateDate) AS CreateYear
FROM	dbo.SouceCategories 
LEFT JOIN dbo.tmpGLSource 
		ON dbo.tmpGLSource.Name = dbo.SouceCategories.SourceCategory
		and dbo.tmpGLSource.ReportStatusID IN (4, 5, 6, 7)
GROUP BY dbo.SouceCategories.SourceCategory
	,MONTH(dbo.tmpGLSource.CreateDate)
	,YEAR(dbo.tmpGLSource.CreateDate)
	,dbo.tmpGLSource.ReportStatusID

Be One with the Optimizer
TG

velkymx
Starting Member

5 Posts

Posted - 2005-12-13 : 17:14:00

Naw - now I am getting duplicate SourceCategories.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-13 : 17:25:14

Don't know of you saw my edit before your post. Here is a GREAT example how to post a question:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59077

Be One with the Optimizer
TG

velkymx
Starting Member

5 Posts

Posted - 2005-12-13 : 17:27:54

All of the requested infor is in the first post.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources