Count() Query Question - 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)

Count() Query Question

Author

Topic

dotnetmonkey
Starting Member

6 Posts

Posted - 2007-05-22 : 05:47:21

OK another query problem that is driving me insane.....

I have the following 2 tables:

Documents
---------
docID(int), docDescription(varchar), dateSent(datetime)

Records
-------
docID(int), userID(int), timesOpened(int)

The idea is that the system sends documents out to users and tracks how many time they have been opened.

I want to produce a summary result set as follows

documentDescription, dateSent, totalSent, totalOpened

I can get the first 3 columns using the following query

Select d.docDescription, d.dateSent, Count(r.userID) as totalSent,
From Documents d Inner Join Records r On d.docID=r.docID
Group By d.docDescription, d.dateSent
Order By d.DateSent

The problem I have is how to get the totalOpened column. The timesOpened field in the records table holds an int for the number of times that the document has been opened. In order to get the totalOpened for my report I would like to add something like..

Count(r.timesOpened > 0) as totalOpened

to my select list. But this is not allowed.

What is the best way to get this result?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-22 : 05:55:54

[code]
Count(case when r.timesOpened > 0 then 1 end) as totalOpened
[/code]

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2007-05-22 : 05:57:53

Use having Clause
Select d.docDescription, d.dateSent, Count(r.userID) as totalSent, Count(r.timesOpened) as totalOpened
From Documents d Inner Join Records r On d.docID=r.docID
Group By d.docDescription, d.dateSent
Having Count(r.timesOpened) > 0
Order By d.DateSent

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-22 : 06:03:49

using HAVING clause would have limit the results return and the value of totalSent will be distorted.

Unless this is what monkey wanted.

dotnetmonkey
Starting Member

6 Posts

Posted - 2007-05-22 : 06:04:02

Thanks for the quick responses. Both give exactly the result I was looking for. Out of interest is there any advantage of using one query over the other?

dotnetmonkey
Starting Member

6 Posts

Posted - 2007-05-22 : 06:06:49

Sorry, yes on closer inspection khtans answer is what I required.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources